0

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')
Improve this question
2
  • 2
    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?!? Commented Nov 18, 2018 at 10:39
  • @MatBailie EXCEPT clause does not work in MySQL :-) Agree about the nth dupe part. Commented Nov 18, 2018 at 10:45

3 Answers 3

Reset to default
3

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

Comments

1

Use NOT IN:

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

I believe the code is easy to understand.

Improve this answer

5 Comments

Yes i've seen many articles about it and here in SO like stackoverflow.com/questions/173041/not-in-vs-not-exists, but I've never experienced any difference in effectiveness and I usually prefer cleaner code.
Even MySQL optimizer will automatically convert an IN to EXISTS clause internally (whereever possible) for further optimization: dev.mysql.com/doc/refman/8.0/en/…
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 in your current query it will not. because it is no correlated, hence Optimizer cannot optimize it further (by using indexing)
My current query does not need any optimization. It will do equally as it is to NOT EXITS.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.