Wednesday, April 15, 2009

Use EXCEPT to select values that does not exist in other table

EXCEPT operation is introduced in SQL Server 2005 and onwards. It is used to rows from left side query that are not present in right side query. So, It may be used to get rows that are present in one table but not in other.
Consider a case that we want to select rows containing name, designation, department from table name staff which are not present in table name Officers.

SELECT nameofStaff, designationofStaff, departmentofStaff
FROM staff

EXCEPT

SELECT nameofOfficer, designationofOfficer, departmentofOfficer
FROM Officers

Above script will provide us distinct records that are there in Staff table but are not present in Officers table. Number of columns should be same on both sides of EXCEPT. And data types of columns should also match.
For more details please visit BOL

No comments:

Post a Comment

Any Comments: