SQL Server equivalent of Oracle''s "MINUS" operator?

Question

Post reply

SQL Server equivalent of Oracle''s "MINUS" operator?

Greg J

SSCarpal Tunnel

Points: 4288
More actions
June 6, 2006 at 12:37 pm

#113368

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

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)

Greg J SSCarpal Tunnel Points: 4288 More actions · Answer 2

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.

Lowell SSC Guru Points: 323504 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David McFarland SSChampion Points: 11815 More actions · Answer 4

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.

stax68 SSChampion Points: 11711 More actions · Answer 5

declare @Tbl1 table(a int, b varchar(10), c varchar(10))
declare
insert
select
select
select
insert
select
select
---------
select null 'MINUS/EXCEPT: like a left anti-join - A&~B' where 1=2
--setwise operator(v9 only):
SELECT
--join
SELECT
FROM
WHERE
---------
select
--setwise operator(v9 only):
SELECT
--join
SELECT
FROM
---------
select
--setwise operator:
SELECT
--join:
SELECT
FROM
---------
select
--join only
SELECT
FROM
where
or t2.a is null

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

David le Quesne SSCertifiable Points: 5229 More actions · Answer 6

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...

Greg J SSCarpal Tunnel Points: 4288 More actions · Answer 7

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

Rob Sanguin SSChasing Mays Points: 610 More actions · Answer 8

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.

stax68 SSChampion Points: 11711 More actions · Answer 9

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

stax68 SSChampion Points: 11711 More actions · Answer 10

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