CODEWITHSUNDEEP
sqlpostgresql
Ramprasad
Ramprasad

Reputation: 8071

select values from a table where exclude values in another table

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: 61849

Answers (3)

Matt
Matt

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

Frank Heikens
Frank Heikens

Reputation: 127576

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

user554546
user554546

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

Related Questions