When doing a subselect with NOT IN, as in
SELECT name
FROM customer
WHERE customer_id NOT IN (
SELECT customer_id
FROM salesorder
);
(from Bruce Momjian's book)
I get no rows if the result column returned by the subselect
contains NULL values. It works as expected if I remove the NULL values
Mr. Joerdens,
I get no rows if the result column returned by the subselect
contains NULL values. It works as expected if I remove the NULL values
from the result set. Is this behaviour correct and if so, why?
I can see how that bug would happen. You may want to forward your
e-mail to
When doing a subselect with NOT IN, as in
SELECT name
FROM customer
WHERE customer_id NOT IN (
SELECT customer_id
FROM salesorder
);
(from Bruce Momjian's book)
I get no rows if the result column returned by the subselect
contains NULL values. It works as expected if I remove the
Frank Joerdens writes:
When doing a subselect with NOT IN, as in
SELECT name
FROM customer
WHERE customer_id NOT IN (
SELECT customer_id
FROM salesorder
);
(from Bruce Momjian's book)
I get no rows if the result column returned by the subselect
contains NULL values. It works as
On Thu, 1 Mar 2001, Frank Joerdens wrote:
When doing a subselect with NOT IN, as in
SELECT name
FROM customer
WHERE customer_id NOT IN (
SELECT customer_id
FROM salesorder
);
(from Bruce Momjian's book)
I get no rows if the result column returned by the subselect
contains NULL
this is kind of weird but it is how it works.
You cannot use equality for null...
Null does not equal Null
Null means no value, since it's not a value
it can't equal anything another no value.
SELECT name
FROM customer
WHERE customer_id NOT IN
(
SELECT customer_id
FROM salesorder
)
and
SELECT name
FROM customer
WHERE NOT EXISTS (
SELECT customer_id
FROM salesorder
WHERE customer_id = customer.customer_id
);
Bruce, you may want to consider editing your next edition to include the
above modification. WHERE ... NOT IN is a bad idea for any subselect on
medium-large
