Correct way to use "NOT IN" Postgres

Question

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;

radar · Accepted Answer · 2015-01-05 16:55:41Z

2

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

answered Jan 5, 2015 at 16:55

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bacon Bits Over a year ago

You may want a SELECT DISTINCT here if one person is allowed to have many vehicles.

Jasen Over a year ago

this executes much faster than where not in

Bacon Bits · Accepted Answer · 2015-01-05 16:58:41Z

2

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)

answered Jan 5, 2015 at 16:58

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Comments

Jayvee · Accepted Answer · 2015-01-05 16:54:45Z

1

another solution, using sets:

Select id From People
except
SELECT person_id FROM Vehicles

answered Jan 5, 2015 at 16:54

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

Comments

Jasen · Accepted Answer · 2015-01-05 20:18:08Z

0

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).

edited Jan 5, 2015 at 20:18

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

answered Jan 5, 2015 at 16:46

SMA

37.2k8 gold badges53 silver badges76 bronze badges

2 Comments

Colton Seal Over a year ago

ahh, nice. This doesnt return anything though. Any suggestions?

user330315 Over a year ago

@ColtonSeal: you most probably have null values in the person_id column. Use select person_id from vehicles where person_id is not null) in the sub-select (the distinct is useless)

Collectives™ on Stack Overflow

Correct way to use "NOT IN" Postgres

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related