# EXCEPT and MINUS

> **❗ Important**: Both the `EXCEPT` and `MINUS` commands perform the exact same operation.

The `EXCEPT` and `MINUS` commands compare the results of two queries and return the distinct rows from the first query that do not appear in the second query.

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.

```sql
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;

```

```output

+----+------------+-----------+---------------------+
| 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 |
+----+------------+-----------+---------------------+

```

```sql
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;

```

```output

+----+------------+------------------------+
| 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.

```sql
SELECT id, product_id FROM ex_orders
MINUS
SELECT id, product_id FROM ex_delivered;

```

```output

+----+------------+
| id | product_id |
+----+------------+
|  1 | xrdf       |
|  3 | qtvb       |
+----+------------+

```

The following query uses `EXCEPT` to deliver the same results.

```sql
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.

```sql
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.

```sql
SELECT *
FROM (SELECT id, product_id FROM ex_orders
      EXCEPT
      SELECT id, product_id FROM ex_delivered) ndel
WHERE ndel.id > 2;

```

```output

+----+------------+
| id | product_id |
+----+------------+
|  3 | qtvb       |
+----+------------+

```

## Related Topics

* [UNION and UNION ALL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/union.md)
* [INTERSECT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/intersect.md)

***

Modified at: June 11, 2026

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/except-and-minus/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/except-and-minus/)

(An index of the documentation is available at /llms.txt)