June 6, 2006 at 12:37 pm
In Oracle, I was able to use a MINUS operator on result sets. It fits in the same place as a UNION would, since it operates on result sets of different SELECT queries. It returns the rows that are in the first query that AREN'T in the second. Prett sure the result set columns had to be identical. Not sure.
This operator could come in handy for me right now. Does anyone know how to effect this in SQL Server 2000?
Eg:
Tbl1 (a, b, c) contains --
1, 'uno', 'one'
2, 'dos', 'two'
3, 'tres','three'
Tbl2 (a, b, c) contains --
2, 'dos', 'two'
SELECT a, b, c FROM Tbl1
MINUS
SELECT a, b, c FROM Tbl2
would yield the following resultset:
1, 'uno', 'one'
3, 'tres','three'
Seems like this could also be effected by using some combinations of joins, but I'm not coming up with it. OUTERs don't give me what I want, CARTESIANs I've probably used 3 times in my career, and INNERs do exactly the opposite of what I want.
Thanks in advance. SQLServerCentral.com rocks the Casbah.
Greg
June 6, 2006 at 12:45 pm
Untested, but this should work for you:
SELECT
Spanish
,English
FROM
Tbl1
WHERE
NOT EXISTS (
SELECT
1
FROM
Tbl2
WHERE
Tbl1.Spanish = Tbl2.Spanish
AND Tbl1.English = Tbl2.English)
June 6, 2006 at 1:05 pm
Thanks David, but I think a "NOT EXISTS" subquery for millions of rows and 30+ columns will take too long for my sitch. I remember MINUS was pretty quick for identical record sets and heavy row counts.
June 6, 2006 at 1:40 pm
isn't this just a full outer join where the right table is null?
SELECT
Spanish
,English
FROM
Tbl1
FULL OUTER JOIN TBL2
ON Tbl1.Spanish = Tbl2.Spanish
AND Tbl1.English = Tbl2.English
WHERE Tbl2.English IS NULL
Lowell
June 6, 2006 at 2:11 pm
Is there any single column that would work? While I realize the above was just sample data, if you have a single unique column that would work between the two tables, such as the first column above, you can use a single column version of my code above, and as long as that column was indexed on your tables, it would be fairly snappy even with the NOT EXISTS.
I'm trying to think of workarounds, and there probably are some that work on a case by case basis, but NOT EXISTS is the typical method of replacing the MINUS operator in moves from Oracle to SQL Server. If it makes any difference, many of us would love to see a MINUS operator (and a FIRST, but that's a different story) in SQL Server, so perhaps it will one day be part of the language.
June 6, 2006 at 8:44 pm
@Tbl2 table(a int, b varchar(10), c varchar(10))
@tbl1
1, 'uno', 'one' union all
2, 'dos', 'two' union all
3, 'tres','three'
@tbl2
2, 'dos', 'two' union all
4, 'quatro', 'four'
a, b, c FROM @Tbl1 except SELECT a, b, c FROM @Tbl2
DISTINCT t.a, t.b, t.c
@Tbl1 t LEFT JOIN @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c
t2.a is null
null 'INTERSECT: like an inner join - A&B' where 1=2
a, b, c FROM @Tbl1 INTERSECT SELECT a, b, c FROM @Tbl2
distinct t.a, t.b, t.c
@Tbl1 t JOIN @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c
null 'UNION: like a full outer join - A|B' where 1=2
a, b, c FROM @Tbl1 union SELECT a, b, c FROM @Tbl2
distinct coalesce(t.a,t2.a) a, coalesce(t.b,t2.b) b, coalesce(t.c,t2.c) c
@Tbl1 t FULL OUTER JOIN @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c
null '"PENUMBRA": like a full outer anti-join - A^B' where 1=2
distinct coalesce(t.a,t2.a) a, coalesce(t.b,t2.b) b, coalesce(t.c,t2.c) c
@Tbl1 t full outer join @Tbl2 t2 on t.a = t2.a and t.b = t2.b and t.c = t2.c
t.a is null
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 7, 2006 at 2:05 am
The MSDN website http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/html/Set-OperationAlternatives.asp gives the following syntax for simulating the ANSI SQL EXCEPT (MINUS) operator in T-SQL
SELECT col1, col2
FROM ( SELECT DISTINCT 'U' AS setname, col1, col2 FROM U
UNION ALL
SELECT DISTINCT NULL, col1, col2 FROM V)
AS D1
GROUP BY col1, col2
HAVING COUNT(*) = 1
AND MAX(setname) = 'U'
with the following explanation...
"The derived table D1 contains distinct rows from each input and a pseudo column called setname, which contains the literal 'U' for U's rows and NULL for V's rows. The code groups the rows from D1 by col1, col2. The COUNT(*) = 1 expression in the HAVING clause ensures that the query returns only the rows that appear in one of the inputs, and MAX(setname) = 'U' ensures that it returns only the rows that appear in U."
David
If it ain't broke, don't fix it...
June 7, 2006 at 5:46 am
ABOVE AND BEYOND. Thanks a heap you guys. I'll have something in place today and I'll update this string with deets.
Thanks again fellas.
Greg
June 7, 2006 at 6:57 am
Response to stax - some nice code there!
Just for completeness I've got a set version of the penumbra, although it seems to be a bit more expensive to execute:-
(SELECT a, b, c FROM @Tbl1 except SELECT a, b, c FROM @Tbl2)
union
(SELECT a, b, c FROM @Tbl2 except SELECT a, b, c FROM @Tbl1).
Rob.
June 7, 2006 at 7:02 am
Oh and by the way (clears throat) if there might be nulls SET ANSI_NULLS OFF for the joins...just a detail...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 7, 2006 at 7:32 am
You also need to identify a non-nullable column on which to do your NULL checks to implement anti-joins... hmm, need to stop posting the first thought that comes into my head.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply