MINUS Keyword in SQL Server - Alternatives

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