Reputation: 8071
I am using PostgreSQL Database. I want to get a column values from a table by excluding the values exists in another table.
select id from mytable where exclude(select id from another table)
In First table available id:
101,102,103,104,105
In second table available id:
101,104
I want result:
102,103,105 (excluded values exist in second table)
How write Query for this?
Upvotes: 22
Views: 61854
Reputation: 337
I tried solution from "user554546" Not sure what was happening on my example but I had to Select Distinct since once I had two values in another_table then my table would show nonfiltered values twice.
So lets say another_table had
|ID|
|03|
|04|
|06|
main_table had
|ID|
|01|
|02|
|03|
|04|
|05|
|06|
After doing the query main_table would show up the following
|ID|
|01|
|01|
|01|
|02|
|02|
|02|
|05|
|05|
|05|
Distinct seems to solve that but any ideas why was this happening?
Upvotes: 0
Reputation: 127596
Using a LEFT JOIN an IS NULL is also an option:
SELECT
id
FROM
mytable
LEFT JOIN another_table ON mytable.id = another_table.id
WHERE
another_table.id IS NULL;
Upvotes: 23
Reputation:
Try
select id
from mytable
where id not in (select id from another_table);
or
select id
from mytable
except
select id
from another_table;
Upvotes: 34