Sqlserver notes: Minus operator in Oracle – Sqlserver Except or Not Exists

http://www.sqlservercurry.com/2011/01/minus-keyword-in-sql-server.html

MINUS operator (in oracle) is used to subtract the rows which are available in the second result, from the first result set. SQL Server does not have a built-in MINUS keyword, but in SQL Server, it’s equivalent is the EXCEPT operator or using NOT EXISTS

Here’s an example. Consider the following tables

DECLARE @table1 table(a int, b int)
DECLARE @table2 table(a int, b int)

TEST DATA

INSERT INTO @table1
SELECT 1 as a, 2 as b
UNION ALL
SELECT 1 as a, 2 as b
UNION ALL
SELECT 11 as a, 12 as b
UNION ALL
SELECT 13 as a, 12 as b

INSERT INTO @table2
SELECT 1 as a, 21 as b
UNION ALL
SELECT 1 as a, 12 as b
UNION ALL
SELECT 1 as a, 112 as b
UNION ALL
SELECT 13 as a, 12 as b

Using EXCEPT

SELECT * FROM @table1
EXCEPT
SELECT * FROM @table2
Using NOT EXISTS

SELECT DISTINCT t1.* FROM @table1 as t1
WHERE NOT EXISTS
(SELECT * from @table2 as t2
WHERE t1.a=t2.a and t1.b=t2.b)

NOT EXISTS also has the same functionality of EXCEPT operator i.e. retrieving the rows from the first table, which are not available in the second result set

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: