37 EXISTS and Derived Tables

EXISTS (subquery) is true when the subquery returns at least one row. It is the workhorse of “does any related row satisfy X?” queries — usually written as a correlated subquery referencing the outer row. A derived table is a subquery in the FROM clause, given an alias and used like a table. The two together cover most of the SQL you write before you reach CTEs and window functions.

What you’ll learn

• EXISTS and NOT EXISTS: tests for “any row exists” / “no row exists”
• Why NOT EXISTS beats NOT IN for nullable subqueries
• Derived tables: a subquery in FROM with an alias
• When a derived table is clearer than a CTE
• The relationship to IN and joins

Example

-- EXISTS: customers who have at least one order
SELECT c.id, c.name
FROM customers_example c
WHERE EXISTS (
  SELECT 1 FROM orders_example o WHERE o.customer_id = c.id
)
ORDER BY c.id;

-- NOT EXISTS: customers with no orders (safe even with NULL customer_id values)
SELECT c.id, c.name
FROM customers_example c
WHERE NOT EXISTS (
  SELECT 1 FROM orders_example o WHERE o.customer_id = c.id
)
ORDER BY c.id;

-- Derived table: a subquery used in FROM, given an alias
SELECT sub.customer_id, sub.total_spent
FROM (
  SELECT customer_id, sum(total) AS total_spent
  FROM orders_example
  GROUP BY customer_id
) AS sub
WHERE sub.total_spent > 30
ORDER BY sub.customer_id;

-- Derived table joined back to a base table
SELECT c.name, latest.total
FROM customers_example c
JOIN (
  SELECT customer_id, max(total) AS total
  FROM orders_example
  GROUP BY customer_id
) AS latest ON latest.customer_id = c.id
ORDER BY c.id;

The convention SELECT 1 inside EXISTS is not magic — PostgreSQL does not care what columns the subquery returns; it only checks whether any row would be produced. SELECT 1, SELECT *, SELECT NULL all work the same. SELECT 1 reads as “I do not care about the columns” and is the conventional choice.

A derived table — also called an inline view — is just a subquery in FROM. An explicit alias (after the closing parenthesis) gives the result a name you can refer to. Derived tables compose well; you can join them, filter them, group them, even nest them. The main downside is that a complex derived table buries the logic in the middle of the query. CTEs (next lesson) are usually a nicer way to name an intermediate result.

Run the example

To run (requires customers_example and orders_example):

$ psql -f source/exists-and-derived-tables.sql postgres
 id | name
----+------
  1 | Alice
(1 row)

 id | name
----+------
  2 | Bob
  3 | Carol
(2 rows)
 ...

Common pitfalls

• IN (SELECT col FROM t) and EXISTS (SELECT 1 FROM t WHERE t.col = outer.col) are usually equivalent — but NOT IN and NOT EXISTS are not when NULLs are possible. Prefer NOT EXISTS whenever NULLs are on the table.
• An alias such as AS x is useful for clarity and portability. PostgreSQL 16 and later allow subqueries in FROM without one; older PostgreSQL versions require an alias.
• A derived table with ORDER BY inside is allowed but the order is not preserved when the outer query consumes it — sort outside.
• Some old guides said EXISTS is “always slower than IN.” On modern PostgreSQL the planner handles both well; choose for correctness and readability first.

Downloads

SQL file: exists-and-derived-tables.sql · All lesson files · Setup guide