Lesson 10.3

SQL EXISTS and NOT EXISTS

EXISTS answers a yes/no question for each outer row: does a matching row exist in another query?

EXISTS keeps a row when the subquery finds at least one match. NOT EXISTS keeps a row when the subquery finds no matches. The selected value inside the subquery doesn't matter; select 1 is a common, readable convention.

This is a good pattern for "has any" or "has none" questions. You avoid accidental row multiplication because the outer query returns one row per outer row, not one row per match.

Pattern

select p.parent_name
from parents as p
where exists (
  select 1
  from children as c
  where c.parent_id = p.parent_id
)

Connect the subquery to the outer row

Most EXISTS checks are correlated: the inner query refers to the current row from the outer query. In this lesson, each customer row is tested against orders for that same customer_id.

Syntax reference

Need syntax details? See the SQL subquery and EXISTS reference

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

Your task

Show customers who have placed at least one order and have no cancelled orders. Return first_name and last_name, sorted by last name and first name.