SQL - set difference and getting fields that aren't a part of the difference
I have a query that basically performs something like this:
select a, b, c
from tab
where tab.state = 'A'
minus
select a, b, c
from tab
where tab.state = 'B'
In this example, a
, b
, and c
are the key fields of this table. state
is also a part of the key, and I'm trying to find situations where there is a record in state A and not in state B. There is another field (not in the key) that I'd like to report on, value
, that might be different for the same record in different states. Example:
a b c state value --------------------- 1 1 1 A 12 1 2 2 A 1002 1 3 9 A 43 1 1 1 B 17.34 1 2 2 B 1002
In this case, I'm interested in the row whose key is 1,3,9
where state is A. I'd also like to get the value of the value
column, but if I try:
select a, b, c, value
from tab
where tab.state = 'A'
minus
select a, b, c, value
from tab
where tab.state = 'B'
What I would get returned is two rows:
a b c value ------开发者_运维技巧---------- 1 1 1 12 1 3 9 43
Basically, I want to have value
in the result set, but not participate in the minus
. I feel like I'm missing something obvious here, but maybe I'm just too tired to get it... ;)
The obvious way to do this is like this:
select a, b, c, value
from tab
where tab.state = 'A' and not exists (
select 1 -- let the optimizer do its thing
from tab ti
where tab.state = 'B' and ti.a=tab.a and ti.b=tab.b and ti.c=tab.c)
I would even add a distinct
in the outer query if the data can have doubles.
You can join all rows where state = 'A'
with the matching ones with state = 'B'
...
SELECT t1.a, t1.b, t1.c, t1.value, t2.value v2
FROM (SELECT a, b, c, value FROM tab WHERE state = 'A') t1
LEFT JOIN (SELECT a, b, c, value FROM tab WHERE state = 'B') t2
ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c
...and then pick the rows where there were no match:
SELECT a, b, c, value
FROM ( /* previous query */ )
WHERE v2 IS NULL
SELECT a,
b,
c,
value
FROM tab tab1
INNER JOIN
(SELECT a, b, c FROM tab WHERE tab.state = 'A'
MINUS
SELECT a, b, c FROM tab WHERE tab.state = 'B'
) tab2
ON tab1.a = tab2.a
AND tab1.b = tab2.b
AND tab1.c = tab2.c
I believe above code would do the trick.
精彩评论