CODEWITHSUNDEEP
mysqlselect
TSG
TSG

Reputation: 4625

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

Upvotes: 10

Views: 50280

Answers (4)

user3021515
user3021515

Reputation: 261

SELECT BAND FROM A WHERE BAND NOT EXISTS(SELECT DISTINCT HATE FROM B)

OR

SELECT BAND FROM A WHERE NOT EXISTS ( SELECT HATE FROM B WHERE A.BAND = B.HATE);

Upvotes: -3

jack
jack

Reputation: 39

SELECT * FROM tableA WHERE id NOT EXISTS (SELECT DISTINCT id FROM tableB)

or

SELECT * FROM tableA WHERE id NOT EXISTS (SELECT id FROM tableB GROUP BY id)

Upvotes: 1

Barranka
Barranka

Reputation: 21057

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

Upvotes: 27

Stephen Fischer
Stephen Fischer

Reputation: 2546

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

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

Upvotes: 8

Related Questions