CODEWITHSUNDEEP
mysqlsql
Jay Welsh
Jay Welsh

Reputation: 496

How to exclude SQL query results

I have two tables, each with a column product_id, I would like to do a query which will exclude results for products with a product_id found in both tables.

Pseudo example:

SELECT *
FROM table1
WHERE product_id NOT IN table2

Upvotes: 0

Views: 64

Answers (2)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

Try this:

select * from table1 
where product_id not in (select product_id from table2) as p;

Upvotes: 0

Shadow
Shadow

Reputation: 34294

If you can have product_ids in both tables that are not present in the other table, then you have to do a union, since mysql does not support full outer join:

select t1.product_id, 't1' as table_name
from table1 t1 left join table2 t2
on t1.product_id = t2.product_id where t2.product_id is null 
union
select t2.product_id, 't2' as table_name
from table1 t1 right join table2 t2
on t1.product_id = t2.product_id where t1.product_id is null

Upvotes: 1

Related Questions