TOP 1 WITH TIES

It was a common question in SQL server newsgroups that how one can implement EXCEPT and INTERSECT set operations in T-SQL. The answer is, When NULLs are not involved, you can achieve the INTERSECT effect by using INNER JOIN and except effect using NOT EXISTS or LEFT JOIN with a NULL check.

But When NULLS are present in data, the above methods will fail because, INTERSECT treats NULLs as equal for matching rows. This NG thread by SQL MVP David Portas discuss some some full equivalents. Also, SQL MVP Itzik Ben Gan has an excellent article on Mimicking INTERSECT and EXCEPT in T-SQL

But those are the days when EXCEPT and INTERSECT were not part of the T-SQL. SQL Sever 2005 adds both these set operators to the language, to make it more powerful.

See this link for for a list of Relational Operators

INTERSECT

INTERSECT is one of the the basic relational operators. INTERSECT works on two sets. It returns rows exists in both sets eliminating duplicates.

INTERSECT ALL also returns rows existis in both the inputs, but if a row repeat n times in the first input and m times in the second input, then the output will contain n or m number of the same row, whichever is less.
Note that both the set should be UNION compatible, that is the number of columns should be same etc..

The ALL version of the INTERSECT is NOT Currently supported in SQL Server 2005.

Enough theory, its time to see the new INTERSECT Operator in action

CREATE TABLE #Skills(Programmer VARCHAR(20), Skill CHAR(3))

INSERT INTO #Skills VALUES('Jaison','NET')
INSERT INTO #Skills VALUES('Roji','NET')
INSERT INTO #Skills VALUES('Roji','SQL')
INSERT INTO #Skills VALUES('Srinivas','SQL')


SELECT * FROM #Skills

Lets get the list of Programmers who has both SQL server and .Net skills

SELECT Programmer FROM #Skills WHERE Skill='NET'
INTERSECT
SELECT Programmer FROM #Skills WHERE Skill='SQL'

Here is the output

Programmer
--------------------
Roji

(1 row(s) affected)

EXCEPT

EXCEPT, also called MINUS returns DISTINCT rows from the LEFT input, which is not present in the RIGHT input.
With EXCEPT ALL, if a row appears more times in the LEFT set than the RIGHT set, then the row will appear in the output n number of times, where n is the number of times the row appear in the left set minus the number of times the row appears in the right set. The ALL version of the EXCEPT is NOT Currently supported in SQL Server 2005.

Here is the EXCEPT version of our above query

Lets get the list of programmers who knows .NEt, but doesnt know SQL.

SELECT Programmer FROM #Skills WHERE Skill='NET'
EXCEPT
SELECT Programmer FROM #Skills WHERE Skill='SQL'

Here is the output.

Programmer
--------------------
Jaison

(1 row(s) affected)