Skip site navigation (1) Skip section navigation (2)

BUG #4422: select ... where ... NOT EXISTS / NOT IN

From: "vasile" <vasile(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4422: select ... where ... NOT EXISTS / NOT IN
Date: 2008-09-17 14:53:51
Message-ID: 200809171453.m8HErpvL084340@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4422
Logged by:          vasile
Email address:      vasile(at)gmail(dot)com
PostgreSQL version: 8.2.4
Operating system:   Centos 4
Description:        select ... where ...  NOT EXISTS / NOT IN
Details: 

I have this 2 similar queries. 

Why the 1st query is returning 1000+ rows and the 2nd one no rows ? 
  The col1 is not empty in both tables. 

1)
SELECT col1, col2, col3
FROM table1 t1
WHERE NOT EXISTS (SELECT t2.col1 FROM table2 t2 WHERE t1.col1 = t2.col1 );

2)
SELECT col1, col2, col3
FROM table1 t1
WHERE t1.col1 NOT IN (SELECT t2.col1 FROM table2 t2 );



If I build the query with "LEFT JOIN" I have the same result set like in the
1st query: 

SELECT t1.col1, t1.col2, t1.col3
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col1 = t2.col1)
WHERE t2.col1 is null;

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2008-09-17 15:24:03
Subject: Re: BUG #4421: convert_to() should be immutable
Previous:From: andipeerDate: 2008-09-17 14:52:12
Subject: BUG #4421: convert_to() should be immutable

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group