INTERSECT and EXCEPT Clause

INTERSECT and EXCEPT Clause:

These are keywords used in SET Theory in Mathematics and the same name is adopted in SQL Server to make coding style easier for developers.

INTERSECT and EXCEPT are one of the exciting feature which will save you tons of time on coding as it is a replacement to IN,EXISTS,NOT IN & NOT EXISTS.

It takes the input and combines them to make a single set.Same as UNION columns in the sets must be equal in type and name.

Syntax:




Where the SET_A and SET_B are select statements, and the SET_OPERATOR is one of the following below

UNION - include all values in SET_A or SET_B.
INTERSECT - include values that exist in SET_A and SET B.
EXCEPT - include values that exist in SET_A and not in SET_B or viceversa.

Let us see a example to better understand this.

Example:-
Consider Users who are defined in managers role whose entries are present in MGR_PERM table and users who are defined in Admin role
are present in ADM_PERM table , we can use the SET Operators to find users who are in either one,both or only one.

CREATE TABLE MGR_PERM
(
USERID VARCHAR(50),
USERNAME VARCHAR(100)
)

CREATE TABLE ADM_PERM
(
USERID VARCHAR(50),
USERNAME VARCHAR(100)
)

INSERT INTO MGR_PERM(USERID)
VALUES ('E0001')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0002')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0003')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0004')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0005')

INSERT INTO ADM_PERM(USERID)
VALUES('E0003')
INSERT INTO ADM_PERM(USERID)
VALUES('E0004')
INSERT INTO ADM_PERM(USERID)
VALUES('E0006')
INSERT INTO ADM_PERM(USERID)
VALUES('E0007')

SELECT * FROM MGR_PERM
SELECT * FROM ADM_PERM

-- List users who have Manager permissions as well as Admin Permissions
SELECT * FROM MGR_PERM
UNION
SELECT * FROM ADM_PERM
-- Result E0001, E0002, E0003, E0004, E0005, E0006, E0007

-- List users who have both Manager and Admin Permissions
SELECT * FROM MGR_PERM
INTERSECT
SELECT * FROM ADM_PERM
-- Result E0003, E0004

-- List users who are managers but not administrators
SELECT * FROM MGR_PERM
EXCEPT
SELECT * FROM ADM_PERM
-- Result E0001, E0002,E0005

-- List users who are administrators but not managers
SELECT * FROM ADM_PERM
EXCEPT
SELECT * FROM MGR_PERM
-- Result E0006, E0007

Difference Between Inner Join and INTERSECT
SELECT * FROM ADM_PERM
INTERSECT
SELECT * FROM MGR_PERM
-- Returns E0003, E0004

SELECT * FROM ADM_PERM
INNER JOIN MGR_PERM
ON ADM_PERM .USERID = MGR_PERM.USERID
AND ADM_PERM.USERNAME = MGR_PERM.USERNAME
-- Returns 0 rows as One NULL is not equal to other NULL