Lesson 14.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 does not 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.

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

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

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_code text

Your task

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

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer