You are viewing an older version of this section. View current production version.
EXCEPT and MINUS
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.
SELECT * FROM orders;
****
+----+------------+-----------+------------+
| id | product_id | stock_qty | order_date |
+----+------------+-----------+------------+
| 2 | sdfv | 4 | 2021-02-10 |
| 4 | fjty | 8 | 2021-03-08 |
| 1 | xrdf | 5 | 2021-01-12 |
| 3 | qtvb | 2 | 2021-02-15 |
+----+------------+-----------+------------+
SELECT * FROM delivered;
****
+----+------------+---------------+
| id | product_id | delivery_date |
+----+------------+---------------+
| 2 | sdfv | 2021-02-14 |
| 4 | fjty | 2021-03-14 |
+----+------------+---------------+
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 orders
MINUS
SELECT id, product_id FROM delivered;
****
+----+------------+
| id | product_id |
+----+------------+
| 1 | xrdf |
| 3 | qtvb |
+----+------------+
The following query uses EXCEPT to deliver the same results.
SELECT id, product_id FROM orders
EXCEPT
SELECT id, product_id FROM delivered;
The following query emulates the EXCEPT operator using the JOIN operator.
SELECT id, product_id
FROM orders
WHERE id NOT IN
(SELECT orders.id
FROM orders INNER JOIN delivered ON orders.id = 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 orders
EXCEPT
SELECT id, product_id FROM delivered) ndel
WHERE ndel.id > 2;
****
+----+------------+
| id | product_id |
+----+------------+
| 3 | qtvb |
+----+------------+
Related Topics
