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
