Re: SQL Humor

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 Sat Aug 20 2005 - 01:35:47 CEST