Database Management · Intermediate level

Intermediate level · Module 2: Advanced SQL Queries · Lesson 1 of 3

Subqueries and EXISTS

Use queries inside queries to filter and calculate, and test for the existence of related rows.

In this lesson you will

  • Write subqueries in WHERE and FROM
  • Use EXISTS and NOT EXISTS for related-row checks

Key concepts

subqueryIN (subquery)correlated subqueryEXISTS

The lesson

A subquery is a query inside another: WHERE amount > (SELECT AVG(amount) FROM payments) finds above-average payments. A subquery in FROM acts like a temporary table.

A correlated subquery refers to the outer row and runs for each one. EXISTS (SELECT 1 FROM distributions d WHERE d.household_id = h.id) is true when a household has at least one distribution.

NOT EXISTS finds rows without related rows, such as households never reached. It handles NULLs more safely than NOT IN, which returns no rows if the subquery contains a NULL.

Step by step

  1. Find payments above the average with a subquery.
  2. List households with at least one distribution using EXISTS.
  3. List households never reached using NOT EXISTS.
  4. Show the NOT IN pitfall with a NULL value.

Practice activity

Answer three programme questions with subqueries and EXISTS.

Expected result: Correct results, including households never reached.

Check your understanding

  1. What does EXISTS test?

    Show answer

    Whether the subquery returns at least one row.

  2. Why prefer NOT EXISTS to NOT IN?

    Show answer

    NOT IN returns no rows if the subquery contains NULL; NOT EXISTS is not affected.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.