January 15, 2011

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

SQL Server MINUS 

You can also give specific columns in your query

OUTPUT

image

Read some more T-SQL Tips over here


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

3 Responses to "MINUS Keyword in SQL Server - Alternatives"
  1. learnersreference com said...
    January 15, 2011 at 11:57 PM

    interesting sql article

  2. William said...
    January 20, 2011 at 1:09 AM
    This comment has been removed by the author.
  3. William said...
    January 20, 2011 at 1:10 AM

    Thanks for the tip! Helped me out a lot.

    Regards,

    managed service provider

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions