Return Values From One Table That Don't Exist In Another

Question

I have two tables, one:

INSERT INTO `fictional_companies` (`ID`, `name`)
VALUES
    (8209948, 'Moulon Rouge LTD'),
    (11399177, 'Burgers Burgers Burgers LTD');

and two:

INSERT INTO `processed` (`ID`)
VALUES
    ('8209948');

I want to return results from the fictional_companies table, but only if they don't exist in the processed table.

In the example case, this would be the result:

(11399177, 'Burgers Burgers Burgers LTD')

NOT EXISTS, or LEFT JOIN, or NOT IN, or EXCEPT... Crikey, did you make ANY attempt to find the answer before you asked this question for the millionth time on SO?!? — MatBailie, Nov 18 '18 at 10:39
@MatBailie `EXCEPT` clause does not work in MySQL :-) Agree about the nth dupe part. — Madhur Bhaiya, Nov 18 '18 at 10:45

score 3 · Answer 1 · answered Nov 18 '18 at 10:36

LEFT JOIN based on proper relationships with IS NULL check on the right side table, will get those rows which are not matching to the right-side table

SELECT fc.*
FROM fictional_companies AS fc
LEFT JOIN processed AS p ON p.ID = fc.ID
WHERE p.ID IS NULL

Another approach is possible using Correlated Subqueries with Not Exists

SELECT fc.*
FROM fictional_companies AS fc
WHERE NOT EXISTS (SELECT 1 FROM processed AS p 
                  WHERE p.ID = fc.ID)

score 2 · Answer 2 · answered Nov 18 '18 at 10:35

2

use left join and check where the null values in right table just pick those values

select t1.* from fictional_companies  t1 
left join processed t2 
on t1.ID=t2.ID
where t2.id is null

answered Nov 18 '18 at 10:35

Zaynul Abadin Tuhin

31,407
5
33
63

score 1 · Answer 3 · answered Nov 18 '18 at 10:36

1

Use NOT IN:

SELECT * FROM `fictional_companies` WHERE `ID` NOT IN (SELECT `ID` FROM `processed`)

I believe the code is easy to understand.

answered Nov 18 '18 at 10:36

forpas

160,666
10
38
76

Yes i've seen many articles about it and here in SO like https://stackoverflow.com/questions/173041/not-in-vs-not-exists, but I've never experienced any difference in effectiveness and I usually prefer cleaner code. – forpas Nov 18 '18 at 10:40
Even MySQL optimizer will automatically convert an `IN` to `EXISTS` clause internally (whereever possible) for further optimization: https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html – Madhur Bhaiya Nov 18 '18 at 10:44
That's exactly my point. If an optimizer will convert NOT IN in such a way as to perform equally like any other solution, let it be. I will write the more readable, cleaner code and let it be optimized. – forpas Nov 18 '18 at 10:46
@forpas in your current query it will not. because it is no correlated, hence Optimizer cannot optimize it further (by using indexing) – Madhur Bhaiya Nov 18 '18 at 10:49
My current query does not need any optimization. It will do equally as it is to NOT EXITS. – forpas Nov 18 '18 at 10:51

Return Values From One Table That Don't Exist In Another

3 Answers3