Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL statement

Re: SQL statement

From: Dan Tow <dantow_at_singingsql.com>
Date: Tue, 26 Oct 2004 13:37:54 -0500
Message-ID: <1098815874.417e99821fbee@www.singingsql.com>


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
Received on Tue Oct 26 2004 - 13:33:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US