CODEWITHSUNDEEP
mysqlsql
Jack Robson
Jack Robson

Reputation: 2302

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

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')

Upvotes: 0

Views: 42

Answers (3)

forpas
forpas

Reputation: 164089

Use NOT IN:

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

I believe the code is easy to understand.

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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)

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

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

Upvotes: 2

Related Questions