Select from table A which does not exist in table B

Question

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".

Answer 1

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

Answer 2

You can use IN, but it's super inefficient:

SELECT * FROM tableA WHERE id NOT IN (SELECT id FROM tableB)