Tutorial Gateway

SQL EXCEPT

by

The SQL EXCEPT is one of the Set operator provided by the SQL Server. This Sql Server except is used to return distinct rows from the left-hand side query that aren’t outputted by the right-hand side query.

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

SQL EXCEPT 8

SQL EXCEPT Syntax

The basic 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 in Sql Server:

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

In this article, we will show you, How to use, or write SQL Except with an example. For this, We are going to use two tables (Employ, and Employees 2016) present in our [SQL Tutorial] Database.

From the below screenshot you can observe that, [Employ] table have ten records

SQL EXCEPT 2

And [Employees 2016] table have four records. Remember that, there is only one distinct record (9, Rob, Verhoff) and the remaining records are same.

SQL UNION Example 2

SQL Except example

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

-- Example for Except in SQL Server
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employ]
 EXCEPT
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2016]

OUTPUT

SQL EXCEPT 3

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

-- Example for Except in SQL Server 
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employees 2016]
  EXCEPT
  SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employ]

OUTPUT

SQL EXCEPT 4

SQL Except Where Clause Example

The following Query will display, How to use the Sql Server except along with the WHERE Clause. In this example, we are combining two statement:

  • First result set – It will select all the records from Employ, whose yearly income is greater than or equal to 70000
  • Second result set – It selects the records from Employees 2016.
  • and the except operator selects existing records from the first result set, that are not present in the second result set.
-- Example for Except in SQL Server
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employ]
WHERE [YearlyIncome] >= 70000
 EXCEPT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employees 2016]

OUTPUT

SQL EXCEPT 5

SQL Except Errors pf

The following Query will display the list of common errors 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 [SQL Tutorial] Database.

From the below screenshot you can observe that [Employee] table have 7 columns and 14 rows.

SQL EXCEPT 9

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

-- Sql Server Except Example
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [SQL Tutorial].[dbo].[Employee]
  EXCEPT
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employ]

OUTPUT

SQL EXCEPT 6

Hope, you can read the message. Now, let us change the query to select an equal number of columns

-- Example for Except SQL  
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employee]
  EXCEPT
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [SQL Tutorial].[dbo].[Employ]

OUTPUT

SQL EXCEPT 7

From the above screenshot you can observe that it is returning 7 rows because, except 2, 7, 9, 11 to 14 records, remaining ten records in Employee are also there in Employ table. And our Except operator select only distinct records from Employee.

Placed Under: SQL