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.orders and delivered, where the orders table lists all the orders and the delivered table lists orders that have been delivered.
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.
SELECT id, product_id FROM ex_ordersMINUSSELECT 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_ordersEXCEPTSELECT id, product_id FROM ex_delivered;
The following query emulates the EXCEPT operator using the JOIN operator.
SELECT id, product_idFROM ex_ordersWHERE id NOT IN(SELECT ex_orders.idFROM 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.MINUS to achieve similar results.
SELECT *FROM (SELECT id, product_id FROM ex_ordersEXCEPTSELECT id, product_id FROM ex_delivered) ndelWHERE ndel.id > 2;
+----+------------+
| id | product_id |
+----+------------+
| 3 | qtvb |
+----+------------+Related Topics
Last modified: March 1, 2023