Select from table A which does not exist in table B

mysql, select

Solution

I would use a join

select A.*
from A left join B on A.BAND = B.HATE
where B.HATE IS NULL;

Remember: Create the appropriate indexes for your table

Problem

I am trying to compose a SELECT statement for MySQL which select from table A what does not exist in table B. For example: Table A: ``` +------+ | BAND | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ ``` Table B: ``` +------+ | HATE | +------+ | 1 | | 5 | +------+ ``` So if table A is all bands, and table B is the bands I hate, then I only want bands I do NOT hate. So the result of a select should be: ``` +------+ | BAND | +------+ | 2 | | 3 | | 4 | +------+ ``` How would I write a single select for this? Here was my last attempt: ``` SELECT * FROM A LEFT JOIN B ON A.BAND = B.HATE WHERE B.HATE IS NULL; ``` EDIT: The line above has been fixed! See comments below..."= NULL" versus "IS NULL".

Original source