MINUS Keyword in SQL Server

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

OUTPUT

You can also give specific columns in your query

OUTPUT

Read some more T-SQL Tips over here

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan