---
title: "EXCEPT"
description: "EXCEPT returns the rows produced by the first query that do not appear in the result of a second query, effectively computing a set difference."
canonical: "https://motherduck.com/glossary/except/"
related:
  - title: "Query syntax | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/query-syntax/"
  - title: "Don't Fear the Agents - AI on the Data Lakehouse"
    url: "https://motherduck.com/blog/dont-fear-the-agents-ai-on-the-data-lakehouse/"
  - title: "What's New in DuckDB 1.5! | MotherDuck"
    url: "https://motherduck.com/videos/whats-new-duckdb-15/"
---

# EXCEPT

> EXCEPT returns the rows produced by the first query that do not appear in the result of a second query, effectively computing a set difference.

## Overview
`EXCEPT` (called `MINUS` in some databases, though not in DuckDB) is a set operation that returns rows from the first `SELECT` query that don't appear anywhere in the second query's results. As with `UNION` and `INTERSECT`, both queries must return the same number of columns with compatible types.

```sql
SELECT customer_id FROM all_customers
EXCEPT
SELECT customer_id FROM churned_customers;
-- customers who have NOT churned
```

## EXCEPT vs NOT IN / NOT EXISTS
`EXCEPT` compares entire rows and is often more concise than an equivalent `NOT IN` or `NOT EXISTS` subquery, especially when comparing on multiple columns at once:

```sql
SELECT customer_id, email FROM crm_contacts
EXCEPT
SELECT customer_id, email FROM suppressed_contacts;
```
Unlike `NOT IN`, `EXCEPT` isn't vulnerable to the `NULL`-poisoning trap where a single `NULL` in the subquery silently zeroes out the whole result.

## EXCEPT vs EXCEPT ALL
Plain `EXCEPT` uses set semantics: duplicates are removed from the output, and a row from the first query is excluded entirely if it matches any row in the second query, regardless of how many times it appears in either side. DuckDB also supports `EXCEPT ALL`, which uses bag semantics -- it removes only as many matching duplicate rows as appear in the second query, keeping the remainder:

```sql
-- table_a has 'x' three times, table_b has 'x' once
SELECT val FROM table_a
EXCEPT ALL
SELECT val FROM table_b;
-- returns 'x' twice
```

This `ALL` variant is part of the ANSI SQL standard but isn't implemented everywhere; DuckDB supports it alongside `UNION ALL` and `INTERSECT ALL` for full bag-semantics set operations.

## Order and column names
Order in `EXCEPT` matters -- `A EXCEPT B` is not the same as `B EXCEPT A`. Column names in the result come from the first query.