MS SQL Server: EXCEPT Operator in SQL Server

The EXCEPT operator in SQL Server helps return the rows in the first SELECT statement that are not returned in the second SELECT statement. Each SELECT statement will have a data set. EXCEPT operator takes the record from the 1st set and removes the results from set 2.

EXCEPT query

EXCEPT query illustration

Explanation: EXCEPT query returns records in the blue area, only in data set 1 and not in data set 2.

Each SELECT statement in the EXCEPT query must have the same number of fields in the result set with the same data type.

EXCEPT operator syntax

 SELECT bieu_thuc1 thuc1, bieu_thuc2, … bieu_thucn 
 FROM bang 
 [WHERE dieu_kien] 
 EXCEPT 
 SELECT bieu_thuc1, bieu_thuc2, … bieu_thucn 
 FROM bang 
 [WHERE dieu_kie n]; 

Variable name or variable value

bieu_thuc

The column or value you want to compare between the SELECT statements. They do not need to be in the same information field at each SELECT statement but the corresponding columns must have the same data.

state

Table wants to get records from there. Must have at least 1 table in the FROM clause.

WHERE dieu_kien

Option. Conditions must satisfy for the selected record.

Note:

• Two SELECT statements must have the same number of expressions.
• The corresponding column in each SELECT statement must have the same data type.
• The EXCEPT operator returns all records from the first SELECT statement and not in the second SELECT statement.
• EXCEPT operator in SQL Server is equivalent to MINUS operator in Oracle.

For instance, - with 1 expression

 SELECT  sanpham_id 
 FROM sanpham 
 EXCEPT 
 SELECT sanpham_id 
 FROM hang tonkho; 

In the example with this EXCEPT operator, the result returns all the sanpham_id values in the table of variables and not in the hangtonkho table. This means that if the sanpham_id value is available on both tables, it will not be returned.

For instance, - with multiple expressions

 SELECT dan hba_id, ho, ten 
 FROM danhba 
 WHERE ho = 'Anderson' 
 EXCEPT 
 SELECT nhanvien_id, ho, ten 
 FROM nhanvien ; 

In this example, the query returns the records in the namba table with the contact ID, the first and last names do not match the employee's ID, last name, and first name in the table.

For instance, - use the ORDER BY clause

 SELECT nh acung_id, nhacung_ten 
 FROM nhacung 
 WHERE bang = 'Florida' 
 EXCEPT 
 SELECT congty_id, congty_ten 
 FROM congty 
 WHERE congty_id <= 400 
 ORDER BY 2;