SQL MINUS

Summary: in this tutorial, you will learn how to use the SQL MINUS operator to find the difference between two SELECT statement.

Introduction to SQL MINUS operator #

The MINUS operator allows you to find the difference between two result sets of two SELECT statements. Here’s the syntax of the MINUS operator:

SELECT
  column1,
  column2
FROM
  table1 
MINUS
SELECT
  column1,
  column2
FROM
  table2;Code language: SQL (Structured Query Language) (sql)

The MINUS operator returns only the rows that appear in the result set of the first SELECT statement but not the second.

Here are the rules for using the MINUS operator:

• Same number of columns: The SELECT statements must have the same number of columns.
• Compatible data types: The corresponding columns in the SELECT statements must have compatible data types.
• Column names: The column names of the result set of the second SELECT statement determines the column names of the final result set.
• Order of execution: The MINUS operator execute each SELECT statement independently and then return the difference.
• Sorting rows in final result set: To sort the final result set, you place an ORDER BY clause in the second SELECT statement.

Basic SQL MINUS operator example #

Suppose we have two tables A and B:

Table A:

Table B:

The following query uses the MINUS operator to find the rows that appear in the first SELECT but not the second one:

SELECT
  id
FROM
  a
MINUS
SELECT
  id
FROM
  b
ORDER BY
  id;Code language: SQL (Structured Query Language) (sql)

Try it

The Playground supports the EXCEPT operator which is equivalent to the MINUS operator.

Output:

 id
----
  1Code language: SQL (Structured Query Language) (sql)

The following picture illustrates how the MINUS operator works in this example:

Finding employees who do not have dependents #

The following picture shows the employees and dependents tables from the sample database.

The following query uses the MINUS operator to find employees who do not have any dependents:

SELECT
  employee_id
FROM
  employees
MINUS
SELECT
  employee_id
FROM
  dependents
ORDER BY
  employee_id;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 employee_id
-------------
         120
         121
         122
         123
         126
         177
         178
         179
         192
         193Code language: SQL (Structured Query Language) (sql)

Summary #

• Use MINUS operator to find the difference between result sets of two queries.

Quiz #

Databases #

• Oracle MINUS Operator