How to select rows based on other tables - MySql

Question

I have tables like this:

CheckTable

(id) (user_id)
[0] [403]

UserTable

(id) (username)
[403] [hello]

I want to select only the rows from the UserTable that do not have the user id row in the CheckTable

I could just select all the users, loop through the rows and then run another query checking if the user_id is in the UserTable, but that would be slow.

Is there any MySQL query that could join and select.

• mustaccio: There certainly is a join query, outer join to be more specific. There is also a NOT EXISTS condition. What have you tried so far?

Answer 1

Is this what you're looking for?

SELECT Whatever FROM UserTable
WHERE UserTable.username NOT IN 
(
 SELECT CheckTable.user_id FROM CheckTable
);

If this is not suitable, please expand. Possibly with table definitions - (SHOW CREATE TABLE My_Table\G) and some sample data - (INSERT INTO My_Table VALUES(....)), and finally, the result you want with the logic you used to obtain it.

With further research, you might also find this interesting - and also this. As a generic response, substituting NOT EXISTS for NOT IN might be the way to go - YMMV.

• Andriy M: Should probably be CheckTable.user_id instead of CheckTable.id. On a different note, I've heard it mentioned more than once that in MySQL the LEFT JOIN + WHERE IS NULL method for some reason works better than either NOT IN or NOT EXISTS. Somewhat odd, I know. But in any event, if I was suggesting NOT IN, I would also make sure to mention it's behaviour with lists containing NULLs.
• Vérace: Your point about NOT NULLs is well made, but I did include two links to discussions about the NULL situation. You are also correct about the field name - should be user_id instead of id - thanks for that (corrected). Maybe a bit strange to have user_id = username - i.e. an INT field = a VARCHAR? No DDL, so who knows?
• Rick James: (NOT) IN ( SELECT ... ) used to evaluate the subquery every time -- very inefficient. 5.6 evaluates it once, builds a tmp table, and conjures up an index -- still more complex than EXISTS or LEFT JOIN.