### Wednesday, July 27, 2005

## EXCEPT AND INTERSECT

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 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, 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)

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)

Comments:

<< Home

Though INTERSECT and EXCEPT are kewl operators .. I don't think MS have added T-SQL support for it! I don't think you examples show here would work as it is.

I even tried your sample and it said "Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'INTERSECT'."

I guess these keywords can be used as it is only for MDX or in analysis services.

I even tried your sample and it said "Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'INTERSECT'."

I guess these keywords can be used as it is only for MDX or in analysis services.

free themeforest templates from Template Plaza. If you need it, just cheek link and I'll pm you direct download link.

www.templatedesk.com

Post a Comment
www.templatedesk.com

<< Home