SQL SERVER EXCEPT

The SQL EXCEPT clause is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

EXCEPT returns only rows, which are not available in the second SELECT statement.

In Select statement Number of columns and their Data Type must be same. Column Names can be different.

Syntax

Select col1 , col2 ,.. from tablename where condition

Except

select col1 ,col2 ,.. from tablename where condition

 

Firstly , we create two tables and insert some common and different records into them.

Create table A
( ID int ,
Name Varchar(100) ,
Code Varchar(50)
);

Insert into A Values (1,'Jhon' ,'XYZ001' ) , 
(2,'Mark' ,'ABC002'), 
(3,'Alfha' ,'ALF003'), 
(4,'Anthony','FGH004') ;

Create table B (
ID int ,
Name Varchar(100) ,
Code Varchar(50
));

Insert into B Values (1,'Jhon' ,'XYZ001' ) ,
(2,'Locus' ,'Loc002'),
(3,'Alfha' ,'ALF003'),
(4,'Jinna','JH004');

Lets select the records from both tables .

SELECT * FROM A 

SELECT * FROM B

As you can see, two rows are common in both table in terms of same values for all columns , So EXCEPT clause

returns only those rows from first table which are not in second table.

 

Select ID ,Name ,Code From A

Except

Select ID ,Name ,Code From B

 

As you can see , Second Row and Fourth row from first table does not exist in second table , so EXCEPT returns only these rows from first tables.

What if you use Except clause only on single column Lets say ID column of both tables .

In this case, it returns an empty result set as for Id column you can see all values are common in both tables so EXCEPT returns only those values for column which are in first table but does not exist in second table.

Select ID  From A

Intersect

Select ID  From B

 

Some Error Cases with EXCEPT

Different Column data types If column data type in select statements for both tables is different , then it returns data type conversions error.

Select ID From A

EXCEPT

Select Code From B

Number of Columns in select statement are not equal  In this case it returns an error for not equal expression

Select ID From A

Except

Select ID, Code From B

 

Also Read

SQL SERVER INTERSECT

SQL SERVER UNION

SQL SERVER UNION ALL

 2,130 total views,  1 views today