Geek asks and answers

Select from table A that does not exist in table B

I am trying to compose a SELECT statement for MySQL that selects from table A something that 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 groups and table B is groups that I hate, then I only need groups that I DO NOT hate. Thus, the result of the selection should be:

+------+
| BAND |
+------+
| 2    |
| 3    |
| 4    |
+------+

How can I write one choice for this? Here is 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".

+5
source share
4 answers

I would use a connection

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

+16
source

IN, :

SELECT * FROM tableA WHERE id NOT IN (SELECT id FROM tableB)
+6
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)
0
source 
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);
-2
source

More articles:


All Articles