Take data that is not in the table

Question

In Mysql I have a table login, that shows who logged on to the system:

id | cod_usuario

After the site is ready and running the client passed me a list of cod_usuario that CAN log into the system, so I created a table can and I did that check every time someone logs in, who’s not on can can’t log in, no problem.

Now the customer wants to know who already logged in the system and who was not in the table can, how to make this check?

It would be something like:

SELECT cod FROM login
LEFT JOIN pode ON pode.cod_usuario = login.cod_usuario

But I don’t know how to get the ones on the table login and ARE NOT on the table can

Answer 1

Check if the return of LEFT JOIN is void:

SELECT cod FROM login
LEFT JOIN pode ON pode.cod_usuario = login.cod_usuario
WHERE pode.cod_usuario IS NULL;

If it is NULL does not exist in the table pode.

Answer 2

1 - Using an undercurrent - has the advantage of being more obvious to the casual reader

SELECT cod FROM login WHERE cod_usuario NOT IN (SELECT cod_usuario FROM pode);

2 - Using left join - can be executed faster than a sub-query and works on all versions of Mysql.

SELECT login.cod FROM login LEFT JOIN pode ON (login.cod_usuario = pode.cod_usuario) WHERE pode.cod_usuario IS NULL;

Answer 3

Another possibility:

SELECT cod 
  FROM login
 WHERE NOT EXISTS (SELECT 1 
                     FROM pode 
                    WHERE pode.cod_usuario = login.cod_usuario)