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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2008-09-17 15:24:03 | Re: BUG #4421: convert_to() should be immutable |
Previous Message | andipeer | 2008-09-17 14:52:12 | BUG #4421: convert_to() should be immutable |