Re: Simple SQL?

Point taken.

On the other hand the query:

(SELECT a.a1,a.a2
FROM a,b
WHERE a.a1 = b.b1
minus
SELECT a.a1,a.a2 FROM a
WHERE a.a1 IN (SELECT b.b1 FROM b))
union
(SELECT a.a1,a.a2 FROM a
WHERE a.a1 IN (SELECT b.b1 FROM b)
minus
SELECT a.a1,a.a2
FROM a,b
WHERE a.a1 = b.b1)

returns no rows, which I could interpret as backup of my position.

I guess at this point we could ask "What is SQL"?

In article <u4eO6.82$EM6.78165_at_news.pacbell.net>, Isaac Blank says...
>
>Try with this data:
>
>
>SELECT A1, A2 from A
>
>A1 A2
>---------- ----------
>0 0
>0 0
>0 1
>1 0
>2 0
>2 2
>
>SELECT B1 FROM B
>
>B1
>----------
>0
>0
>3
>
>
>"Mikito Harakiri" <nospam_at_newsranger.com> wrote in message
>news:SRcO6.2122$r4.113543_at_www.newsranger.com...
>> You are not saying that sql with IN and EXIST constructs is more powerful
 than
>> without them, arent't you? Because if I use joins with MINUS operation I
 cover
>> 'NOT IN' and 'NOT EXISTS' perfectly well. And I don't think I undertand
 Issak's
>> comment why unique key is important. Here is my set of data:
>>
>> SELECT A1, A2 from A
>>
>> A1 A2
>> ---------- ----------
>> 0 0
>> 0 1
>> 1 0
>> 2 0
>> 2 2
>>
>> SELECT B1 FROM B
>>
>> B1
>> ----------
>> 0
>> 3
>>
>> SELECT a.a1,a.a2 FROM a
>> WHERE a.a1 IN (SELECT b.b1 FROM b)
>>
>> A1 A2
>> ---------- ----------
>> 0 0
>> 0 1
>>
>> Here natural join works just fine:
>>
>> SELECT a.a1,a.a2
>> FROM a,b
>> WHERE a.a1 = b.b1
>>
>> A1 A2
>> ---------- ----------
>> 0 0
>> 0 1
>>
>> 'NOT IN' example:
>>
>> SELECT a.a1,a.a2 FROM a
>> WHERE a.a1 NOT IN (SELECT b.b1 FROM b)
>>
>> A1 A2
>> ---------- ----------
>> 1 0
>> 2 0
>> 2 2
>>
>> SELECT a.a1,a.a2
>> FROM a
>> minus
>> SELECT a.a1,a.a2
>> FROM a,b
>> WHERE a.a1 = b.b1
>>
>> A1 A2
>> ---------- ----------
>> 1 0
>> 2 0
>> 2 2
>>
>> Some counterexample data, please.
Received on Sun Jul 22 2001 - 01:25:01 CEST