EXCEPT returns any distinct values from the left query not found on the right query.
INTERSECT returns any distinct values returned by both the query on the left and right sides of the INTERSECT operand.
The basic rules for combining the result sets of two queries that use EXCEPT / INTERSECT are as follows:
a. The number and the order of the columns must be the same in all queries.
b. The data types must be compatible.
Below is the easy example to understand how we can get matching record using INTERSECT from department and Employee tables, which shows only matching department in Employee tables.
By using EXCEPT we get unmatched rows from department to Employee table, where not department id exist in Employee table.
CREATE TABLE DepartmentMaster (DepartmentID int identity(1,1), DepartmentName
varchar(50))
GO
CREATE TABLE EmployeeMaster (ID int identity(1,1), EmployeeName Varchar(50), EmployeeAge int, DepartmentId int)
GO
INSERT INTO DepartmentMaster(DepartmentName) VALUES ('IT'), ('Operation'), ('Account'), ('HR'), ('Admin')
GO
INSERT INTO EmployeeMaster(EmployeeName, EmployeeAge, DepartmentId) VALUES ('Ram Singh', 25, 1), ('Krishna Paresh', 25, 1), ('Govind Bedi', 25, 2), ('Shyam Singh', 25, 1), ('Giri Pasawan', 25, 3)
GO
-- MATCHED ROWS FROM
DepartmentMaster AND EmployeeMaster
SELECT
DepartmentID, DepartmentName
FROM DepartmentMaster
INTERSECT
SELECT EM.DepartmentID, DM.DepartmentName FROM EmployeeMaster EM LEFT OUTER JOIN DepartmentMaster DM ON DM.DepartmentID = EM.DepartmentId
GO
--UNMATCHED ROWS FROM
DepartmentMaster AND EmployeeMaster
SELECT
DepartmentID, DepartmentName
FROM DepartmentMaster
EXCEPT
SELECT EM.DepartmentID, DM.DepartmentName FROM EmployeeMaster EM LEFT OUTER JOIN DepartmentMaster DM ON DM.DepartmentID = EM.DepartmentId
Enjoy!
Comments