SQL 42 🛢️ Finding Differences with EXCEPT and MINUS - KandZ

Every set operation answers a question about two collections. UNION asks “what is in either?” INTERSECT asks “what is in both?” And EXCEPT asks “what is in the first but not the second?” This last question—finding what is missing, what has changed, what exists in one place but not another—is one of the most common analytical needs in SQL. The EXCEPT operator answers it directly.

This chapter covers EXCEPT and its synonym MINUS. You will learn the syntax, the column compatibility rules that govern it, how it handles duplicates, and why some databases call it EXCEPT while others call it MINUS. You will see how EXCEPT differs from NOT IN and NOT EXISTS, both of which can produce similar results but with different semantics around nulls. And you will work through the practical cases where EXCEPT is the clearest way to express a query.

By the end, you will understand when set difference is the right tool, when a join or anti-join is better, and how to avoid the null-handling trap that trips up developers using NOT IN.

Key point: EXCEPT compares entire rows and returns rows from the first query that do not appear in the second. Both queries must return the same number of columns in the same order with compatible types. Duplicates are removed. The order of the operands matters—A EXCEPT B is not the same as B EXCEPT A.

Why EXCEPT exists

The difference problem. Set difference is a fundamental operation in relational algebra. When you have two lists and need to know what is in one but not the other, you are computing a difference. This pattern appears constantly in practice: customers who have not ordered, products that are not in stock, users who have not logged in, records that exist in a staging table but not in production. Each of these is a set difference.

The null trap problem. The most common way to express “not in” in SQL is the NOT IN operator. But NOT IN has a well-known trap: if the subquery returns even a single NULL, the entire predicate evaluates to NULL—which is treated as false—and the query returns no rows . This surprising behavior has caused countless bugs. EXCEPT does not have this problem because it compares rows using set semantics, not three-valued logic. A NULL in one result set is treated as equal to a NULL in the other for the purposes of determining which rows are common.

The readability problem. Consider the query “find products that have never been ordered.” You could write this with NOT IN, with NOT EXISTS, or with a LEFT JOIN ... WHERE ... IS NULL. All three work. But EXCEPT states the logic more directly: take the set of all products, subtract the set of products that appear in orders, and return what remains. When the intent is genuinely a set difference, EXCEPT communicates it with less cognitive overhead than an anti-join.

The standard naming problem. The SQL standard defines the operator as EXCEPT. Oracle, however, calls it MINUS. Both do the same thing. PostgreSQL, SQL Server, SQLite, and MariaDB support EXCEPT. Oracle and MySQL support MINUS (though MySQL’s support is limited). Knowing which name to use on which platform is a practical necessity .

The performance question. EXCEPT is not always the fastest option. On large tables, NOT EXISTS with a proper index often outperforms EXCEPT because it can short-circuit. But EXCEPT is frequently the clearest, and clarity has value. The right choice depends on the specific query, the indexes available, and the readability needs of the code.

a. Basic syntax and column rules

The syntax of EXCEPT mirrors UNION and INTERSECT. Two SELECT statements with EXCEPT between them.

SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2;

The result contains rows that appear in the first result set but not in the second. The order of operands matters: A EXCEPT B returns rows in A that are not in B. B EXCEPT A returns rows in B that are not in A. These are different results.

The column rules are the same as for INTERSECT:

Both queries must return the same number of columns.
The columns must be in the same order.
The data types must be compatible.

-- Valid: same column count and types
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

-- Invalid: different column counts
SELECT customer_id, name FROM customers
EXCEPT
SELECT customer_id FROM orders;  -- error

Column names in the result come from the first query. The comparison is positional, not by name. The first column of the first query is compared to the first column of the second query, and so on.

b. Duplicates and EXCEPT ALL

Like INTERSECT, EXCEPT removes duplicates by default. If a row appears three times in the first result set and not at all in the second, it appears once in the output.

-- customers contains customer_id 1 three times
-- orders does not contain customer_id 1
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;
-- Result: customer_id 1 appears once

Standard SQL defines EXCEPT ALL, which preserves duplicates based on the difference in occurrence counts. If a row appears three times in the first result and once in the second, EXCEPT ALL returns it twice (3 – 1 = 2). If it appears once in the first and three times in the second, it does not appear at all.

-- PostgreSQL
SELECT customer_id FROM customers
EXCEPT ALL
SELECT customer_id FROM orders;

Support for EXCEPT ALL varies. PostgreSQL and Oracle support it. SQL Server and SQLite do not . Most use cases want the default behavior, which removes duplicates.

c. EXCEPT versus NOT IN and NOT EXISTS

The three ways to express “rows in A not in B” produce similar results but differ in semantics and performance.

EXCEPT compares complete rows as sets. It handles nulls by treating them as equal for comparison purposes. It removes duplicates automatically.

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

NOT IN compares a single column against a list or subquery. It has a critical flaw: if the subquery returns any NULL, the entire predicate becomes NULL, and no rows are returned.

SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- If orders.customer_id contains any NULL, this returns zero rows

NOT EXISTS is a correlated subquery that checks for the absence of matching rows. It handles nulls correctly and is often the fastest option when the comparison column is indexed.

SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

The choice among these depends on the situation. EXCEPT is clearest when comparing complete rows. NOT EXISTS is safest when nulls are possible and performance matters. NOT IN should be used only when you are certain the subquery cannot return nulls—which is rarely a safe assumption.

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE customers (
  customer_id INT,
  name VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT,
  customer_id INT
);

INSERT INTO customers VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Carol'),
  (4, 'Dave');

INSERT INTO orders VALUES
  (100, 1),
  (101, 3),
  (102, 1);

-- ============================================
-- PART 2: BASIC EXCEPT
-- ============================================
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;
-- Result: 2, 4 (customers who have not ordered)

-- ============================================
-- PART 3: ORDER MATTERS
-- ============================================
SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM customers;
-- Result: empty (all order customers exist in customers)

-- ============================================
-- PART 4: DUPLICATES REMOVED
-- ============================================
-- customers has each id once; orders has customer_id 1 twice
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;
-- Result: 2, 4 (not 2, 4 with duplicates)

-- ============================================
-- PART 5: COMPARISON WITH NOT IN
-- ============================================
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Same result: 2, 4

-- ============================================
-- PART 6: COMPARISON WITH NOT EXISTS
-- ============================================
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Same result: 2, 4

-- ============================================
-- PART 7: THE NULL TRAP WITH NOT IN
-- ============================================
INSERT INTO orders VALUES (103, NULL);

SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Result: empty! The NULL in orders makes all comparisons NULL

-- ============================================
-- PART 8: EXCEPT HANDLES NULL CORRECTLY
-- ============================================
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;
-- Result: 2, 4 (NULL does not affect the result)

-- ============================================
-- PART 9: MULTI-COLUMN EXCEPT
-- ============================================
SELECT customer_id, name FROM customers
EXCEPT
SELECT customer_id, 'Alice' FROM orders WHERE customer_id = 1;
-- Compares both columns positionally

-- ============================================
-- PART 10: THREE-WAY EXCEPT
-- ============================================
CREATE TABLE inactive_customers (customer_id INT);
INSERT INTO inactive_customers VALUES (4);

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM inactive_customers;
-- Result: 2 (customers who haven't ordered and aren't inactive)

The ten parts covered the essential behavior of EXCEPT: sample data, basic difference, operand order, duplicate handling, comparison with NOT IN and NOT EXISTS, the null trap, null-safe behavior, multi-column comparison, and chained EXCEPT.

Quick Reference

Set Operators

Operator	Returns	Duplicates
`UNION`	Rows from both	Removed
`UNION ALL`	Rows from both	Preserved
`INTERSECT`	Rows in both	Removed
`EXCEPT` / `MINUS`	Rows in first, not second	Removed
`EXCEPT ALL`	Rows in first, not second	Count difference

EXCEPT Rules

Rule	Requirement
Column count	Must match
Column order	Must match
Data types	Must be compatible
Column names	Taken from first query
Join keys	Not used; positional comparison
Operand order	Matters—`A EXCEPT B` ≠ `B EXCEPT A`
Order	Undefined unless `ORDER BY` added

Database Naming and Support

Database	Name	EXCEPT ALL
PostgreSQL	`EXCEPT`	Yes
SQL Server	`EXCEPT`	No
Oracle	`MINUS`	Yes (as MINUS ALL)
SQLite	`EXCEPT`	No
MySQL	`EXCEPT` (8.0.31+)	No

EXCEPT vs NOT IN vs NOT EXISTS

Aspect	EXCEPT	NOT IN	NOT EXISTS
Null-safe	Yes	No	Yes
Duplicates	Removed	N/A	Depends
Multi-column	Yes	No	Yes (with row constructor)
Performance	Moderate	Variable	Often fastest
Readability	High for set logic	Moderate	Moderate

Best Practices

✅ Do This:

-- Use EXCEPT for clear set difference
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;                             -- ✅

-- Add ORDER BY for deterministic output
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders
ORDER BY customer_id;                                       -- ✅

-- Use NOT EXISTS when performance matters and nulls exist
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);              -- ✅

-- Verify operands are in the correct order
-- A EXCEPT B returns rows in A not in B                    -- ✅

❌ Don’t Do This:

-- Use NOT IN with a nullable subquery
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);  -- ❌ if orders has NULL

-- Mismatched column counts
SELECT customer_id, name FROM customers
EXCEPT
SELECT customer_id FROM orders;                             -- ❌

-- Expect duplicates in output
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;                             -- ❌ (no duplicates)

-- Forget that order matters
SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM customers;                          -- ❌ (reversed operands)

Common Pitfalls

Pitfall	Why It Happens	Fix
Query returns no rows	`NOT IN` with nullable subquery	Use `EXCEPT` or `NOT EXISTS`
Wrong difference returned	Operands reversed	Ensure `A EXCEPT B` order
Syntax error on Oracle	Using `EXCEPT` instead of `MINUS`	Use `MINUS` on Oracle
Mismatched columns	Different column counts	Align both queries
Duplicates expected	`EXCEPT` removes them	Use `EXCEPT ALL` if supported
Slow performance	`EXCEPT` on large tables	Use indexed `NOT EXISTS`

Real-World Examples

1. Customers Who Have Not Ordered

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

2. Products Not in Inventory

SELECT product_id FROM products
EXCEPT
SELECT product_id FROM inventory;

3. Users Who Have Not Logged In

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM login_history;

4. Records in Staging but Not Production

SELECT record_id FROM staging_table
EXCEPT
SELECT record_id FROM production_table;

5. Missing Permissions

SELECT permission_id FROM all_permissions
EXCEPT
SELECT permission_id FROM user_permissions WHERE user_id = 42;

6. Emails Not Subscribed

SELECT email FROM customers
EXCEPT
SELECT email FROM newsletter_subscribers;

7. Multi-Column Difference

SELECT first_name, last_name FROM employees
EXCEPT
SELECT first_name, last_name FROM contractors;

8. Oracle MINUS Syntax

SELECT customer_id FROM customers
MINUS
SELECT customer_id FROM orders;

9. Chained EXCEPT

SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b
EXCEPT
SELECT id FROM table_c;

10. EXCEPT with ORDER BY

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders
ORDER BY customer_id;

Visual

Set Operations Compared

┌─────────────────────────────────────────────────────────────┐
│  SET OPERATIONS ON TWO RESULT SETS                          │
│                                                             │
│  Set A: {1, 2, 3, 4}                                        │
│  Set B: {3, 4, 5, 6}                                        │
│                                                             │
│  UNION:          {1, 2, 3, 4, 5, 6}   (all rows)            │
│  INTERSECT:      {3, 4}               (common rows)         │
│  EXCEPT (A-B):   {1, 2}               (A minus B)           │
│  EXCEPT (B-A):   {5, 6}               (B minus A)           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

EXCEPT Operand Order

┌─────────────────────────────────────────────────────────────┐
│  ORDER MATTERS                                              │
│                                                             │
│  A = {1, 2, 3, 4}                                           │
│  B = {3, 4, 5, 6}                                           │
│                                                             │
│  A EXCEPT B                                                 │
│  → Rows in A that are not in B                              │
│  → {1, 2}                                                   │
│                                                             │
│  B EXCEPT A                                                 │
│  → Rows in B that are not in A                              │
│  → {5, 6}                                                   │
│                                                             │
│  Different results. Order is not symmetric.                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The NULL Trap

┌─────────────────────────────────────────────────────────────┐
│  WHY NOT IN FAILS WITH NULL                                 │
│                                                             │
│  Query: WHERE id NOT IN (1, 2, NULL)                        │
│                                                             │
│  For id = 3:                                                │
│    3 <> 1  → TRUE                                           │
│    3 <> 2  → TRUE                                           │
│    3 <> NULL → NULL (unknown)                               │
│                                                             │
│  TRUE AND TRUE AND NULL = NULL                              │
│  WHERE treats NULL as false                                 │
│  → id = 3 is excluded                                       │
│                                                             │
│  Result: no rows returned, even though 3 is not in the list │
│                                                             │
│  EXCEPT does not have this problem.                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

EXCEPT vs NOT EXISTS

┌─────────────────────────────────────────────────────────────┐
│  EXCEPT                                                     │
│                                                             │
│  SELECT customer_id FROM customers                          │
│  EXCEPT                                                     │
│  SELECT customer_id FROM orders;                            │
│                                                             │
│  → Set difference, null-safe, removes duplicates            │
│  → Clear intent, moderate performance                       │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  NOT EXISTS                                                 │
│                                                             │
│  SELECT c.id FROM customers c                               │
│  WHERE NOT EXISTS (                                         │
│    SELECT 1 FROM orders o WHERE o.customer_id = c.id        │
│  );                                                         │
│                                                             │
│  → Correlated anti-join, null-safe                          │
│  → Often faster with index on o.customer_id                 │
│  → More verbose                                             │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
Operator	`EXCEPT` returns rows in first query not in second
Alias	`MINUS` (Oracle)
Column rule	Same count, order, compatible types
Duplicates	Removed by default
`EXCEPT ALL`	Preserves count difference
Comparison	Positional, not by join key
Result columns	From first query
Operand order	Matters—not symmetric
Ordering	Undefined unless `ORDER BY` added
Null handling	Treats nulls as comparable
MySQL support	`EXCEPT` in 8.0.31+; no `EXCEPT ALL`

Key takeaways:

EXCEPT computes set difference. It returns rows that appear in the first query but not the second. The operands are not interchangeable; order determines which direction the difference runs.
Column compatibility is strict. Both queries must return the same number of columns, in the same order, with compatible types. Comparison is positional.
Duplicates are removed by default. EXCEPT treats results as sets. EXCEPT ALL preserves the count difference where supported.
EXCEPT is null-safe; NOT IN is not. If the subquery in a NOT IN returns any NULL, the entire predicate evaluates to NULL and no rows are returned. EXCEPT treats nulls as comparable and does not have this trap.
NOT EXISTS is often faster. When the comparison column is indexed, NOT EXISTS can short-circuit. EXCEPT may require full row comparison. Choose based on performance testing.
Oracle calls it MINUS. The syntax is otherwise identical. MINUS and EXCEPT do the same thing; the name is a platform convention.
Add ORDER BY for deterministic output. Without it, the order of the result is undefined and may vary between executions.

Remember: EXCEPT is the clearest way to express “what is in A but not in B” when that is genuinely the question. It avoids the null trap that makes NOT IN dangerous and reads more directly than a correlated NOT EXISTS. But it is not always the fastest option on large tables, and it is not available under the same name on every database. Know the operator, know its alternatives, and choose deliberately based on the data, the indexes, and the readability needs of the query.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!