Table A data not contained in Table B

Question

Following tables:

Table: Users

Id , Name

Table: Card

Id, Idusuario, Descricao

I make the query to know which user has no card:

SELECT * 
FROM usuarios as u 
LEFT JOIN cartao as c ON c.IdUsuario != u.Id

The result does not return only users who have no card, returns also those who have card.

I would like to return only users without card.

Answer 1

Doing without subquery, just make a WHERE and return all who have the idCartao null. It would look like this:

SELECT * 
       FROM usuario as u 
       LEFT JOIN cartao as c ON (c.IdUsuario = u.Id)//Altere aqui para igual
WHERE c.id is null//retorne todos que não possuem cartão

Answer 2

I don’t know about Mysql, but in SQL Server it is possible to write in 3 different ways:

NOT IN

SELECT usuarios.* 
FROM usuarios 
WHERE id NOT IN(
    SELECT IdUsuario 
    FROM cartao
)

NOT EXISTS

SELECT usuarios.* 
FROM usuarios 
WHERE id NOT EXISTS(
    SELECT IdUsuario 
    FROM cartao
)

LEFT JOIN combined with IS NULL

SELECT usuarios.* 
FROM usuarios 
LEFT JOIN cartao ON usuarios.id = cartao.IdUsuario 
WHERE cartao.IdUsuario IS NULL

again, I don’t know about Mysql, but in SQL Server, the alternatives NOT IN and NOT EXISTS perform better than LEFT JOIN.

It is also worth remembering that this result (better or worse) will also depend on the version of the Bank Engine, which can be slow in an old version, can be fast in a more current version.

In the link below there is a detail (SQL Server) about the statement I made above (in any case you should make your own tests using your environment):

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

Answer 3

Can use a NOT IN() to know which users do not have cards associated:

SELECT * FROM usuarios WHERE id NOT IN(SELECT IdUsuario FROM cartao)

The query can be translated as, select all users where the id does not exist (or does not match) in the card table.

Answer 4

I think the simplest way is this:

SELECT * 
FROM usuarios as u 
LEFT JOIN cartao as c ON c.IdUsuario = u.Id where IdUsuario is Null

Be happy :D