Get values from one table that are not contained in another

Question

I would like to know how I can get values from a mysql database and the data obtained should be in one table and not in another.

For example: I want all equipment (equipment table) that is not reserved (reservation table)

Answer 1

You don’t give a lot of information but if I understand it well it should do what you want:

SELECT * FROM equipamentos e
WHERE e.id NOT IN (SELECT equipamentoId FROM reservas);  

I assume that in the reserve table has the id of the equipment that is reserved.

See working on Sqlfiddle

Another way to do the same:

SELECT * FROM equipamentos e
WHERE NOT EXISTS (SELECT * FROM reservas r WHERE e.id = r.equipamentoId);

See working on Sqlfiddle

A short explanation:

A SELECT only returns lines that make the clause WHERE true.

In the first example each equipment id is checked if you are not (NOT IN) in the list returned by sub-query SELECT equipamentoId FROM reservas.
If not, the clause WHERE is true and the SELECT returns that line.

In the second example each equipment id is checked if there is no (NOT EXISTS) on the table reservations.
NOT EXISTS is true if the sub-query SELECT * FROM reservas r WHERE e.id = r.equipamentoId not return lines, clause WHERE is true and the SELECT returns that line.

Answer 2

Another way to get this information is through LEFT JOIN or RIGHT JOIN Observe:

Table A contains the fields a,b,c Table B contains the fields a,b,c,d,e,f

SELECT A.a, A.b, A.c, B.d, B.e, B.f
  FROM A
 RIGHT JOIN B ON A.a = B.a AND A.b = B.b AND A.c = B.c
 WHERE B.d IS NULL OR B.e IS NULL

Note that unlike the IN criterion used in the previous answer this method accepts more fields in the WHERE criterion

This method is very common when we try to compare two or more tables that have similar information