Data from a table A not contained in Table B

2

Following tables:

Table: Users

Id, Name

Table: Card

Id, UserID, Description

I ask to know which users do not have a card:

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

The result does not only return users who do not have a card, it also returns those who have a card.

I would like to return only users without a card.

    
asked by anonymous 13.07.2016 / 20:12

4 answers

5

Doing without subquery, just make a WHERE and return all that have 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
    
13.07.2016 / 20:45
4

You can use NOT IN() to know which users do not have associated cards:

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

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

    
13.07.2016 / 20:15
4

I do not 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 do not know about MySQL, but in SQL Server, the alternatives NOT IN and NOT EXISTS perform better when compared to 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, it can be fast in a more current version.

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

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

    
13.07.2016 / 20:46
1

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

    
13.07.2016 / 20:48