WHERE EXISTS (subquery) keeps an outer row when the subquery returns even one row. Since it checks only “whether a row exists”, regardless of what the subquery returns, SELECT 1 is sufficient inside it.

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1                       -- the value does not matter; only row existence is checked
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- a “correlated subquery” that references an outer column
);

From the users table, retrieve users with at least one order whose status is 'completed'. Return user_id, name, plan, ordered by user_id ascending.

Expected output (user_id ascending):

Note: user 2 has only pending orders; user 4 has only cancelled orders; user 5 has no orders. All are excluded.

SELECT
  user_id, name, plan
FROM   users u
WHERE  EXISTS (                   -- TRUE if the subquery returns even one row (short-circuit evaluation)
  SELECT 1                       -- the returned value does not matter; only row existence is checked
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- correlated condition referencing the outer u.user_id (required)
    AND  o.status  = 'completed'
)
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                → process one row at a time
  2. EXISTS(...)                 → run the subquery for each user
  3. SELECT user_id, name, plan  → select rows that pass
  4. ORDER BY user_id            → sort by user_id ascending
  */

WHERE NOT EXISTS (subquery) keeps an outer row when the subquery returns zero rows. It is the negation of EXISTS and is the pattern for checking that no corresponding row exists in another table.

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id   -- correlated condition
);

From the users table, retrieve users with no completed orders using NOT EXISTS. Return user_id, name, plan, ordered by user_id ascending.

Expected output (user_id ascending):

Note: users 1 and 3 have completed orders and are excluded. User 2 has only pending orders; user 4 has only cancelled orders; user 5 has no orders, so all three pass.

SELECT
  user_id, name, plan
FROM   users u
WHERE  NOT EXISTS (            -- pass when the subquery returns zero rows (nothing exists)
  SELECT 1
  FROM   orders o
  WHERE  o.user_id = u.user_id -- correlated condition referencing the outer column (required)
    AND  o.status  = 'completed'
)
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                → process one row at a time
  2. NOT EXISTS(...)             → run the subquery for each user
  3. SELECT user_id, name, plan  → select rows that pass
  4. ORDER BY user_id            → sort by user_id ascending
  */

Inside EXISTS, you can JOIN multiple tables to perform a more complex existence check. This pattern uses one EXISTS to check a two-step relationship, such as “users who ordered a product in this category.”

WHERE EXISTS (
  SELECT 1
  FROM   orders o
  JOIN   products p ON o.product_id = p.product_id
  WHERE  o.user_id  = u.user_id           -- correlation with the outer query
    AND  p.category = 'Smartphone'  -- category condition
)

From the users table, retrieve users who have ever ordered at least one product in the 'PC' category. Return user_id, name, plan, ordered by user_id ascending.

Expected output (user_id ascending):

Note: order 102 (user 1, product 2: PC) and order 105 (user 4, product 2: PC) match. Users 2 and 3 have no PC orders.

SELECT
  user_id, name, plan
FROM   users u
WHERE  EXISTS (
  SELECT 1
  FROM   orders o
  JOIN   products p ON o.product_id = p.product_id  -- join to product information
  WHERE  o.user_id  = u.user_id               -- correlated condition with the outer users
    AND  p.category = 'PC'                   -- check whether a PC-category product was ordered
)
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                → process one row at a time
  2. EXISTS(...)                 → check for a PC order for each user
  3. SELECT user_id, name, plan  → select rows that pass
  4. ORDER BY user_id            → sort by user_id ascending
  */

Practical queries express complex conditions by combining multiple predicates. Learn the three-stage filter pattern of narrowing a numeric range with a comparison predicate, selecting specific categories with IN, and checking for an order with EXISTS.

WHERE p.price       >= 50000               -- ① comparison predicate: price range
  AND p.category    IN ('PC', 'Tablet')  -- ② IN predicate: category selection
  AND EXISTS (                               -- ③ EXISTS predicate: check for an order
    SELECT 1 FROM orders o
    WHERE  o.product_id = p.product_id
      AND  o.status = 'completed'
  )

From the products table, retrieve products where price is at least 50,000 and category is 'PC' or 'Tablet', and where at least one completed order exists. Return product_id, name, category, price, ordered by price descending.

Expected output (price descending):

Note: product 1 is excluded because it is in the Smartphone category; product 3 is a Tablet but has no completed order (only pending); products 4 and 5 are excluded because they are Accessories and below the price floor.

SELECT
  product_id, name, category, price
FROM   products p
WHERE  p.price    >= 50000              -- ① comparison predicate: at least 50,000 (index-friendly)
  AND  p.category IN ('PC', 'Tablet')    -- ② IN predicate: target categories only
  AND  EXISTS (                         -- ③ EXISTS predicate: check for a completed order
    SELECT 1
    FROM   orders o
    WHERE  o.product_id = p.product_id  -- correlated condition with the outer product
      AND  o.status      = 'completed'  -- check completed orders only
  )
ORDER BY p.price DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products p                           → read 6 rows
  2. WHERE p.price threshold                   → filter by price
  3. AND p.category IN (...)                   → filter by category
  4. AND EXISTS (...)                           → filter to products with completed orders
  5. SELECT product_id, name, category, price  → select 4 columns
  6. ORDER BY p.price DESC                     → sort by price descending
  */