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.
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.
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.
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?
(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.
Commented
Feb 15, 2016 at 23:49
NOT EXISTScondition. What have you tried so far?