Sign inTry Free

EXCEPT and MINUS

On this page

Important

Both the EXCEPT and MINUS commands perform the exact same operation.

EXCEPT and MINUS are synonymous operators which can be used to compare the results of two queries and return the distinct rows from the first query that are not output by the second.

When comparing two result sets using EXCEPT or MINUS, the data types must be compatible and the number and order of the columns should match across queries.

Syntax

SELECT ...
{ EXCEPT | MINUS }
SELECT ...

Arguments

Accepts two or more queries that return data of comparable data types.

Remarks

In addition to being able to be used in queries, EXCEPT and MINUS can also be used in subqueries and common table expressions (CTEs).

The column names of the returned result set are the same as the column names returned by the query on the left side of the operator.

If using ORDER BY, column names must match those returned by the left-side query.

Examples

The following examples demonstrate how to compare two result sets using EXCEPT or MINUS.

Example 1

Consider the following scenario. You have two tables orders and delivered, where the orders table lists all the orders and the delivered table lists orders that have been delivered. You need a list of all the orders that haven’t been delivered yet.

CREATE TABLE ex_orders (id int, product_id varchar(10), stock_qty int, order_date datetime);
INSERT INTO ex_orders VALUES
  (2,'sdfy',4,'2021-02-10'),
  (4,'fjty',8,'2021-03-08'),
  (1,'xrdf',5,'2021-01-12'),
  (3,'qtvb',2,'2021-02-15');


SELECT * FROM ex_orders;
+----+------------+-----------+---------------------+
| id | product_id | stock_qty | order_date          |
+----+------------+-----------+---------------------+
|  2 | sdfv       |         4 | 2021-02-10 00:00:00 |
|  4 | fjty       |         8 | 2021-03-08 00:00:00 |
|  1 | xrdf       |         5 | 2021-01-12 00:00:00 |
|  3 | qtvb       |         2 | 2021-02-15 00:00:00 |
+----+------------+-----------+---------------------+
CREATE TABLE ex_delivered (id int, product_id varchar(10), delivery_date datetime);
INSERT INTO ex_delivered VALUES
  (2,'sdfy','2021-02-14'),
  (4,'fjty','2021-03-14');


SELECT * FROM ex_delivered;
+----+------------+------------------------+
| id | product_id | delivery_date.         |
+----+------------+------------------------+
|  2 | sdfv       | 2021-02-14 00:00:00    |
|  4 | fjty       | 2021-03-14 00:00:00    |
+----+------------+------------------------+

The following query lists all the orders that haven’t been delivered yet. Note: To match the number and order of columns, each column is specified in the query.

SELECT id, product_id FROM ex_orders
MINUS
SELECT id, product_id FROM ex_delivered;
+----+------------+
| id | product_id |
+----+------------+
|  1 | xrdf       |
|  3 | qtvb       |
+----+------------+

The following query uses EXCEPT to deliver the same results.

SELECT id, product_id FROM ex_orders
EXCEPT
SELECT id, product_id FROM ex_delivered;

The following query emulates the EXCEPT operator using the JOIN operator.

SELECT id, product_id
FROM ex_orders
WHERE id NOT IN
    (SELECT ex_orders.id
    FROM ex_orders INNER JOIN ex_delivered ON ex_orders.id = ex_delivered.id);

Example 2

The following example shows how to use EXCEPT in a subquery, using the tables orders and delivered from the previous example. You can also use MINUS to achieve similar results.

SELECT *
FROM (SELECT id, product_id FROM ex_orders
      EXCEPT
      SELECT id, product_id FROM ex_delivered) ndel
WHERE ndel.id > 2;
+----+------------+
| id | product_id |
+----+------------+
|  3 | qtvb       |
+----+------------+

Related Topics

Last modified: March 1, 2023

Was this article helpful?

On this page

Was this article helpful?