Different between NOT IN and NOT EXISTS in postgres SQL

Question

Here's my table.
info.student_info table

When I execute the below query using NOT IN it gives me namal and Ann.

SELECT firstname 
FROM info.student_info 
WHERE firstname NOT IN (SELECT firstname 
                        FROM info.student_info 
                        WHERE lastname IS NULL)

But when I execute the below query using NOT EXISTS it gives me no rows.

SELECT firstname 
FROM info.student_info 
WHERE NOT EXISTS (SELECT firstname 
                  FROM info.student_info 
                  WHERE lastname IS NULL)

Why is this? I have researched some areas on NOT IN and NOT EXISTS, but couldn't find an answer for this.

In the second case, the correlated subquery is not corelated to the main query; it returns True if there does not exist any row with lastname IS NULL. — wildplasser
– wildplasser, Commented Jan 29, 2014 at 9:34
The usual answer is NULL, but your NOT IN already filters out NULLs. @wildplasser accurately points out the difference. — Craig Ringer
– Craig Ringer, Commented Jan 29, 2014 at 9:37

Jakub Kania · Accepted Answer · 2014-01-29 09:20:31Z

22

NOT IN is true when value is not in the set returned by the subquery. NOT EXISTS is true when the subquery doesn't return anything.

answered Jan 29, 2014 at 9:20

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

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

1 Comment

namalfernandolk Over a year ago

Thank you Jakub. It tell's everything!

Incognito · Accepted Answer · 2014-01-29 09:26:10Z

4

You have not joined the tables in your NOT EXISTS query and that is why, you're not getting any result from your second query.

Your second query would have returned results only in case when the sub-query could not find record at all.

change your query to join on the firstname column of the outer table, and then you might get result.

SELECT firstname
FROM info.student_info
WHERE NOT EXISTS (SELECT b.firstname
                  FROM   info.firstname b
                  WHERE  b.lastname IS NULL
                  AND    b.firstname = a.firstname
                 )

Disclaimer: Not tested the query

answered Jan 29, 2014 at 9:26

Incognito

3,1043 gold badges30 silver badges42 bronze badges

Comments

Klas Lindbäck · Accepted Answer · 2014-01-29 09:20:51Z

3

Your NOT EXISTS doesn't discriminate on firstname, so the subselect will always return 2 rows.

To do the same thing with NOT EXISTS you need to use the following SQL:

SELECT firstname FROM info.student_info 
WHERE NOT EXISTS 
  (SELECT firstname f2 FROM info.student_info
    WHERE lastname IS NULL 
      AND f2 = firstname)

answered Jan 29, 2014 at 9:20

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

1 Comment

namalfernandolk Over a year ago

Thank you! Honestly, I had an misunderstanding on the IN and EXISTS. I was enlightened with Jakub's answer. Apart from that here the result is same.

Collectives™ on Stack Overflow

Different between NOT IN and NOT EXISTS in postgres SQL

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related