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