SQL EXCEPT 1

SQL EXCEPT

The SQL EXCEPT is one of the Set operators used to return distinct rows from the left-hand side query that aren’t outputted by the right-hand side query.

Or, we can say, SQL Server except will return all the records from a left table, that is not present in the right side. The below image will help you to understand the Except.

EXCEPT ven diagram 8

Syntax

The syntax behind the SQL Server Except is as shown below:

SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table1
EXCEPT
SELECT Column_Name1, Column_Name2 ......., Column_NameN FROM Table2

The following are the set of rules for except operator:

  1. The total columns must be the same in both the queries.
  2. The column data types must be compatible with each other.
  3. Order of the Columns must be the same in all the queries.
SQL EXCEPT 1

For this Sql Server Except operator demo, We are going to use two tables (Employ, and Employees 2016) present in our Database. From the below screenshot, you can observe that the [Employ] table has ten records

Employ Table for except demonstration 2

And [Employees 2016] table has four records. In this SQL Server table, there is only one distinct record (9, Rob, Verhoff) and the remaining records are the same.

Second table data for except demo 2

SQL Except example

The following query will return total records from Employ that are not present in Employees2016 table and display the result

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employ]
 EXCEPT
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2016]
SQL EXCEPT 3

Let us alter the table position. It means this except query returns total the records from Employees2016 table that is not present in Employ table

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2016]
  EXCEPT
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employ]
SQL EXCEPT 4

SQL Except Where Clause Example

How to use this operator with the WHERE Clause. In this example, we are joining two statements:

  • The first result set selects all the records from Employ, whose yearly income is greater than or equal to 70000
  • The second result set picks the records from Employees 2016.
  • And this operator selects existing records from the first result set that are not present in the second result set.
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
WHERE [YearlyIncome] >= 70000
 EXCEPT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employees 2016]
SQL EXCEPT Where clause 5

Except Errors pf

The following query will display the list of common errors pf that we encounter while writing the SQL Server except query. For this, We are going to use two tables (Employee, and Employ) present in the Database. Our [Employee] table has 7 columns and 14 rows.

SQL EXCEPT errors pf 9

Let us see what will happen when we perform except operation on an unequal length of columns.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  EXCEPT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
Messages
--------
Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPToperator 
must have an equal number of expressions in their target lists.

Now, let us modify the query to select an equal number of columns

 SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  EXCEPT
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employ]
SQL EXCEPT with an equal number of columns 7

From the above image, see that it is returning seven rows because, except 2, 7, 9, 11 to 14 records, the remaining ten records in Employee are also there in the Employ table. And our operator selects only distinct records from Employees.