| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL statement
ahh, null values ... please, allow me to join the conversation!
Dan, talking about NOT IN vs. NOT EXISTS:
you could argue that the NOT IN does the "right thing"
considering we are in the world of three-valued logic,
whereas the NOT EXISTS is doing the "wrong thing" ...
formally stated:
the EXISTS operator does not correspond with the iterated OR.
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Dan Tow
Sent: Tuesday, October 26, 2004 19:38
To: george.rusnak_at_deca.mil
Cc: 'oracle-l_at_freelists.org'
Subject: Re: SQL statement
I have the answer, I think. If you do
select count(*) from login;
I think you'll get 3 or 4, indicating that QID is NULL for one or two rows
of
login. The NOT IN condition is equivalent (once the duplicates are
discarded)
to saying
SELECT qid
FROM question
WHERE qid NOT IN (1, NULL)
which in turn is equivalent ot saying
SELECT qid
FROM question
WHERE NOT (qid = 1 OR qid = NULL)
In the case of question.qid=1, (qid = 1 OR qid = NULL) = TRUE, and NOT TRUE
=
FALSE, so that row clearly won't come back. More subtly, though, in the
other
cases, where qid = 2 and qid = 3,
NOT (qid = 1 OR qid = NULL)
evaluates to
NOT (TRUE OR UNKNOWN) which evaluates to
NOT UNKNOWN which evaluates to
UNKNOWN
and Oracle won't return a row where the WHERE clause evaluates to UNKNOWN
any
more than it will return a row where the WHERE clause evaluates to FALSE.
The trick is to recognize that
<expr> = NULL
(or <expr> > NULL, or <expr> != NULL, or <expr> LIKE NULL, ...)
will always evaluate to the truth-value UNKNOWN in SQL's peculiar
three-valued
logic, and UNKNOWN has most of the properties of FALSE, *except* that NOT
UNKNOWN is also UNKNOWN, while NOT FALSE is TRUE. I mention this gotcha in
SQL
Tuning, O'Reilly, p. 212, and it is one of the best reasons to make a
general
practice of converting NOT IN subqueries to the almost-equivalent NOT EXISTS
form (where this very counter-intuitive behavior does not come up).
Thanks,
Dan Tow
650-858-1557
www.singingsql.com
Quoting "Rusnak, George A. (SEC-Lee) CTR" <george.rusnak_at_deca.mil>:
> Does anyone have any ideas why this is not working ???
>
> pweb:acedmgr> select QID from login;
>
> QID
> ----------
> 1
> 1
>
>
>
> pweb:acedmgr> select qid from question;
>
> QID
> ----------
> 2
> 3
> 1
>
> pweb:acedmgr> SELECT qid
> FROM question
> 2 WHERE qid NOT IN (SELECT qid FROM login);
>
> no rows selected
> ============================================================
>
> pweb:acedmgr> desc login
> Name Null? Type
> ----------------------------------------- -------- ---------------
> USER_ID NOT NULL VARCHAR2(20)
> PSWD NOT NULL VARCHAR2(32)
> PSWD_CHG_DTE DATE
> LAST_NAME NOT NULL VARCHAR2(30)
> FIRST_NAME NOT NULL VARCHAR2(30)
> LOCKED VARCHAR2(1)
> CREATED_BY NOT NULL VARCHAR2(20)
> CREATED_DTE NOT NULL DATE
> QID NUMBER(6)
> ANSWER VARCHAR2(100)
> USER_ROLE VARCHAR2(1)
>
> pweb:acedmgr> desc question
> Name Null? Type
> ----------------------------------------- -------- --------------
> QID NOT NULL NUMBER(6)
> QUESTION NOT NULL VARCHAR2(200)
>
> =================================================================
>
> TIA
>
> Al Rusnak
> DBA - CISIS, Computer Operations
>
> * 804-734-8210
> * george.rusnak_at_deca.mil
>
> --
> http://www.freelists.org/webpage/oracle-l
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 26 2004 - 13:46:31 CDT
 |
 |