Re: SQL statement
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: george.rusnak@xxxxxxxx
- Date: Tue, 26 Oct 2004 13:37:54 -0500
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@xxxxxxxx>:
> 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@xxxxxxxx
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
