Return the Set Difference of a tuple, how?? -- sql server programming

Brad
7/29/2004 6:52:27 PM

Here's a stumper:

I have two queries, on two different tables, that return a set tuple,
ie,

select distinct a, b from table_1
select distinct c, d from table_2

How do I combine the queries to return the set difference of (a, b) -
(c, d)??

The example to perform a set difference in the Sql Server books is to use a
not exists clause, something like:

select distinct a, b from table_1
where not exists (select distinct c, d from table_2)

but this always returns no rows, ie, the empty set :(

anyone have any ideas on how to perform the set difference between two
tuples in Sql Server?

I know there are set functions with Multidimensional Expressions (MDX), but
the database eventually needs to be ported to other DB platforms, so I would
rather stay away from writing MDX queries if I can't port them (also I am
not familiar at all with MDX anyway :-/)

Thanks.

Vishal Parkar
7/30/2004 12:56:19 AM

try:

select distinct a, b from table_1 y
where not exists (select * from table_2 x
where x.c = y.a and x.d=y.b)

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

[quoted text, click to view]

Steve Kass
7/30/2004 1:32:27 AM

Mischa,

Your query is not so efficient if table_1 is empty and table_2
contains millions of rows, and in fact, I suspect the DISTINCT will be a
problem in many situations. With indexes on (a,b) and (c,d), Vishal's
query will work well, assuming that a and b are constrained to be NOT
NULL.

If a or b is NULLable, however, while there may be some debate on what
set difference means, your query is the only posted solution that will
produce what might be considered the expected results.

This uses the same idea, and might run slightly in some cases, but
probably not as fast as Vishal's on indexed tables.

SELECT a, b
FROM (
SELECT a, b, 1 AS flag FROM table_1
UNION ALL
SELECT c, d, -1 AS flag FROM table_2
) X
GROUP BY a, b
HAVING MIN(flag) = 1

Steve Kass
Drew University

[quoted text, click to view]

Mischa Sandberg
7/30/2004 2:33:23 AM

[quoted text, click to view]
Eh? The logic of this query is, if table_2 is not empty, return nothing.
If table_2 is empty, return all distinct a,b from table_1.

Need a really efficient way to find set difference?

SELECT a, b, SUM(flag) -- (1)in table_1 only (-1) in table_2 only (0) in
both.
FROM (
SELECT DISTINCT a, b, 1 AS flag FROM table_1
UNION ALL
SELECT DISTINCT c, d, -1 AS flag FROM table_2
) X
GROUP BY a, b
HAVING SUM(flag) = 1 --- or <> 0, or whatever.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Return the Set Difference of a tuple, how??