CODEWITHSUNDEEP
sqlpostgresql
Colton Seal
Colton Seal

Reputation: 379

Correct way to use "NOT IN" Postgres

I have two tables, People, and Vehicles. Vehicles belongs to people. Im trying to check if a person does not have a vehicle. I was attempting to do this by joining People and Vehicles, and displaying the persons ID that is NOT IN Vehicles.person_id.

This is returning nothing, and has me wondering if there is something I did wrong, or if there is a more efficient way of doing this.

Query is below

Select People.id From People INNER JOIN Vehicles on People.id=Vehicles.person_id where People.id NOT IN Vehicles.person_id;

Upvotes: 0

Views: 108

Answers (4)

SMA
SMA

Reputation: 37023

Use Subquery as below:

Select id 
From People
WHERE id NOT IN (SELECT distinct person_id
                 FROM Vehicles 
                 WHERE  person_id IS NOT NULL)

select all people who are not in (by Select id From People WHERE id NOT IN) the List of all the people who has vehicle by SELECT distinct person_id FROM Vehicles (you could avoid null as well here if you want).

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32145

NOT IN can have issues with NULL values, and should probably be avoided for performance reasons if the subquery is very large.

Try NOT EXISTS:

SELECT p.id
FROM People p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Vehicles v
    WHERE v.person_id = p.id)

Upvotes: 2

radar
radar

Reputation: 13425

Use left join to figure out the persons with no vehicles

  Select distinct People.id 
  From People 
  LEFT JOIN Vehicles on        People.id=Vehicles.person_id 
  where Vehicles.person_id is NULL

Upvotes: 2

Jayvee
Jayvee

Reputation: 10875

another solution, using sets:

Select id From People
except
SELECT person_id FROM Vehicles

Upvotes: 1

Related Questions