CODEWITHSUNDEEP
mysql
Dalbin Shimy
Dalbin Shimy

Reputation: 85

Select value that doesn't exists in two tables

table1            table2             table3          Expected Result
-------------     --------------     -------------   ------
| id | col1 |     | id | col1  |     | id | col1 |   | id |
-------------     --------------     -------------   ------
| 1  | val1 |     | 2  | val1  |     | 3  | val1 |   | 1  |
| 2  | val2 |     | 2  | val2  |     | 3  | val2 |   | 6  |
| 3  | val3 |     | 4  | val1  |     | 5  | val1 |   ------
| 4  | val4 |     | 4  | val2  |     | 5  | val2 |
| 5  | val5 |     --------------     ------------
| 6  | val6 |
-------------

I want to select the id of table1 which are not in table2 and table3. For that, I have written a query, but it takes too much of time to execute. Please suggest me with a better query.

the query I have used is SELECT DISTINCT(id) FROM table1 WHERE yacht_id NOT IN (SELECT id FROM table2 UNION SELECT id FROM table3 ORDER BY id)

Upvotes: 2

Views: 123

Answers (3)

beejm
beejm

Reputation: 2481

SELECT * FROM table1 a
LEFT JOIN table2 b on a.id = b.id
LEFT JOIN table3 c on a.id = c.id
WHERE b.id is null and c.id is null

Can you try this

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Why not try two separate In Clause 

SELECT id
FROM table1 
WHERE yacht_id NOT IN (SELECT id FROM table2) 
   OR yacht_id NOT IN (SELECT id FROM table3)

Upvotes: 1

Stefano Zanini
Stefano Zanini

Reputation: 5916

Your query is fine, but an alternative is using left joins

select  t1.id
from    table1 t1
left join
        table2 t2
on      t1.id = t2.id
left join
        table3 t3
on      t1.id = t3.id
where   t2.id is null and t3.id is null

This will preserve all rows from table1, matching them with nulls when the id is not available on table2 or table3.

From there all you have to do is filtering for rows with null on both table2 and table3.

Upvotes: 2

Related Questions