TSG TSG - 6 months ago 28
MySQL Question

Select from table A which does not exist in table B

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 Source

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