Oppolzer - Informatik / Blog

Thank you very much.

You saved three people here from getting mad about this problem,
thank's a lot. Together some 60 years of DB2 experience, but we
never before met this problem ...

Kind regards

Bernd



Am 13.09.2012 15:13, schrieb W.J.:
> P.
> Wow! That is the detailed explanation of my admittedly very short
> answer, why these two queries have different result sets.
>
> Mit freundlichen Grüßen
> W.J.
>
>
>
> ------------------------------------------------------------------------
> *Von: unknown
> *Gesendet:* Donnerstag, 13. September 2012 15:03
> *An:* DB2-L@lists.idug.org
> *Betreff:* [DB2-L] - RE: Problem with non-correlated subquery
>
> The following variant for the noncorrelated subquery will return the
> same rows as the correlated one:
>
> select bensl
> from table1
> where bensl not in
> (select bensl
> from table2
> where bensl IS NOT NULL);
>
> Explanation of the originally 0 rows: a query of the form
>
> SELECT ... FROM ... WHERE col1 NOT IN (val1, val2, NULL)
>
> always returns zero rows, on any standard-complying relational system,
> because of the 3-valued logic of SQL:
>
> a certain predicate is either true (for a table row), or false, or
> unknown (and we often forget about that 3rd case).
> a WHERE with such a predicate will only return rows for which the
> predicate is true, not those for which it's uncertain.
> Especially with negatively formulated predicates, this can be
> counter-intuitive.
>
> Most common example is of course "WHERE col1 <> val1", which does
> *not* return rows where col1 has NULL.
> The idea is that for those rows it's "uncertain" or "unknown" whether
> the real value in col1 is val1 or not (since it's not (yet) filled in).
>
> Back to the "not in" example: why does it not return a row where col1
> has value val3 (different from val1 and val2)?
> Because the empty entry ("NULL") in the list *could* be val3 (but not
> yet filled in). So it's unknown whether val3 is IN that list or not.
>
>
> In Reply to bernd oppolzer:
>
> why do the following two queries yield different results?
>
> select bensl
> from table1
> where bensl not in
> (select bensl
> from table2);
>
> 0 rows
>
> select bensl
> from table1 t1
> where not exists
> (select 1
> from table2
> where bensl = t1.bensl);
>
> many rows
>
> -----End Original Message-----
>
> -----End Original Message-----