Lesson 15.4

SQL EXCEPT

EXCEPT subtracts one compatible result set from another.

EXCEPT returns rows from the first query that don't appear in the second query. It reads like subtraction: start with a full list, then remove the rows found by another SELECT.

This can express missing-match questions clearly, especially when the final output is a simple list of values or identifiers.

Pattern

select value_column
from full_list
except
select value_column
from values_to_remove

The selected row is the comparison

EXCEPT compares every selected column. Keep both sides aligned and avoid adding extra columns to only one side of the query.

Schema · Garden Shop Tables · orders, customers 2 tables

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Syntax reference

Need syntax details? See the SQL set operations reference

Your task

Return customer_id, first_name, and last_name for active customers who have never had a shipped order.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: customer_id, first_name, last_name.
Rows: active customers with no shipped order rows.
EXCEPT compares the full selected row, so both SELECT statements use the same columns.

← Previous · 15.3 INTERSECT

End of Chapter 15

That's the last lesson in “Set Operations.”

The next chapter builds on what this one covered.

Start Chapter 16: Summary Reports and Pivots → Begins with 16.1 Pivoting with conditional aggregation

Reference

SQL JOIN Types

INNER, LEFT, RIGHT, FULL, and CROSS joins.

Cheatsheet

SQL Joins Cheatsheet

Join types and common join patterns.

Interview