Kenhuang Academy
  • English

      • SQL Subqueries

      Overview

      Subqueries are queries nested inside other SQL queries. They can appear in WHERE, FROM, SELECT clauses, enabling complex data retrieval logic to be completed in a single SQL statement.

      WHERE Subqueries

      IN Subquery

      IN checks if a value exists in the result set returned by the subquery:

      -- Query all products in the "Electronics" category
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE name = 'Electronics'
);

      NOT IN Subquery

      NOT IN excludes values in the subquery result:

      -- Query users who have no cancelled orders
SELECT * FROM users
WHERE id NOT IN (
    SELECT user_id FROM orders WHERE status = 'cancelled'
);

      EXISTS Subquery

      EXISTS checks if the subquery returns at least one row:

      -- Query users who have at least one order
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

      NOT EXISTS Subquery

      NOT EXISTS checks if the subquery returns no data:

      -- Query products with no reviews
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.product_id = p.id
);

      FROM Subqueries (Derived Tables)

      Subqueries can be placed in the FROM clause as temporary tables:

      -- Query category statistics with more than 10 products
SELECT * FROM (
    SELECT
        category,
        COUNT(*) AS product_count,
        AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE product_count > 10;

      SELECT Subqueries (Scalar Subqueries)

      Subqueries can be placed in the SELECT clause as computed columns:

      -- Query each user with their order count
SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

      Correlated Subqueries

      Correlated subqueries reference columns from the outer query and are re-executed for each row:

      -- Query the highest priced product in each category
SELECT p.* FROM products p
WHERE p.price = (
    SELECT MAX(price)
    FROM products
    WHERE category = p.category
);

      Summary

      Subquery key points:

      • WHERE clause can use IN, EXISTS operators
      • Subqueries in FROM clause are used as derived tables
      • Scalar subqueries in SELECT clause are used for computed columns
      • Correlated subqueries reference outer query columns