🪆 Subqueriesintermediate

How to use subqueries in SQL

Use a query inside another query to filter, compute, or transform data.

Subquery in WHEREAll databases

SELECT name, email
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE total > 1000
);

The inner query returns a list of user IDs, the outer query filters by that list.

Subquery in FROM (derived table)All databases

SELECT
  country,
  avg_age
FROM (
  SELECT
    country,
    AVG(age) AS avg_age
  FROM users
  GROUP BY country
) AS country_stats
WHERE avg_age > 30;

A subquery in FROM is called a derived table or inline view. Must have an alias.

In most cases a CTE (WITH clause) is cleaner than a subquery in FROM.

Scalar subquery in SELECTAll databases

SELECT
  name,
  (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

A correlated subquery that runs once per row. Returns one value per user.

Correlated subqueries run once per row — can be slow on large tables. Use JOIN + GROUP BY instead when possible.

EXISTS subqueryAll databases

SELECT name, email
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
    AND o.total > 500
);

EXISTS returns true if the subquery finds any matching row. Faster than IN for large datasets.

When to use: Checking for existence of related records. Often faster than IN with large result sets.

Common Mistakes

→Correlated subqueries in SELECT run once per row — use JOIN + GROUP BY for better performance
→Subqueries in FROM must have an alias

Common Mistakes

Related Topics