Re: Simple SQL?

One thing I could say is that interpretation is always in the eyes of the beholder.

On a more serious note - when doing set operations, you implicitely eliminate duplicates, that's why you get these counterintuitive results. I wonder what the standard says...

In article <53fO6.2335$r4.132474_at_www.newsranger.com>, Mikito Harakiri says...
>
>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:04 CEST