Re: Simple SQL?

Cool. I refactored your "Exclusive-OR for bags" version a little bit:

select a1, a2, sum(cnt) from (
SELECT a.a1,a.a2, count(1) cnt
FROM a,b
WHERE a.a1 = b.b1
group by a.a1,a.a2
union
SELECT a.a1,a.a2, -count(1) cnt
FROM a
WHERE a.a1 IN (SELECT b.b1 FROM b)
group by a.a1,a.a2
) group by a1, a2

A1 A2 SUM(CNT)
---------- ---------- ----------

0          0          2
0          1          1

In article <8TfO6.2426$r4.137578_at_www.newsranger.com>, Mikito Harakiri says...
>
>SELECT a.a1,a.a2, count(1)
>FROM a,b
>WHERE a.a1 = b.b1
>group by a.a1,a.a2
>minus
>SELECT a.a1,a.a2, count(1)
>FROM a
>WHERE a.a1 IN (SELECT b.b1 FROM b)
>group by a.a1,a.a2
>union
><swap them>
>
>In article <XgfO6.2365$r4.133875_at_www.newsranger.com>, Vadim Tropashko says...
>>
>>In article <53fO6.2335$r4.132474_at_www.newsranger.com>, Mikito Harakiri says...
>>>
>>>(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)
>>>
>>
>>Hmm... That's how I compared 2 queries as well. I guess I need to revise my
>>practice... Wait a minute, does anybody has a solution how to compare bags in
>>SQL?
>>
Received on Sun Jul 22 2001 - 01:25:34 CEST