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

Home -> Community -> Usenet -> comp.databases.theory -> Re: SQL Humor

Re: SQL Humor

From: Mikito Harakiri <mikharakiri_nospaum_at_yahoo.com>
Date: 19 Aug 2005 16:35:47 -0700
Message-ID: <1124494547.485886.181440@g47g2000cwa.googlegroups.com>

Hugo Kornelis wrote:
> On 18 Aug 2005 15:13:28 -0700, Mikito Harakiri wrote:
>
> (snip)
> >BTW, you triggered the other example: is EXISTS or IN faster?
>
> Woops - forgot to add this to my previous reply:
>
> Before thinking about speed, you should think about the difference
> between the two. IN can result in UNKNOWN; EXISTS can only result in
> TRUE or FALSE. Since UNKNOWN and FALSE are treated the same in a WHERE,
> WHEN, or HAVING clause, many people doon't notice the difference - until
> they start combining IN with NOT and getting unexpected results!
>
> Only choose the faster version if they truly are equal!!

This tiny difference is really a pain in the butt. This UNNKNOWN based logic is rubbish, and any query involving NULLs still could give a surprising result. Therefore, the life would be much simpler if SQL standard just defined NOT IN and NOT EXIST to be identical. After all, they both are just dumbed down aggregate scalar subquery expressions:

--IN, EXIST
select * from dept d
where 0<(select count(*) from emp

         where dept.deptno=emp.deptno)

--NOT IN, NOT EXIST
select * from dept d
where 0=(select count(*) from emp

         where dept.deptno=emp.deptno)

(both also known as semijoin and antijoin). Aggregate scalar subquery syntax is more general, and generic solution always rule. Received on Fri Aug 19 2005 - 18:35:47 CDT

Original text of this message

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