What are subqueries in SQL?

A subquery (or nested query) in SQL is a SELECT query embedded within another SQL statement (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE). The subquery is executed first, and its result is used by the outer query.

• Usage:

  • In the WHERE clause for filtering data.
  • In the FROM clause as a virtual table (derived table).
  • In the SELECT clause to output aggregated or related data (scalar subquery).
  • With IN, EXISTS, ANY, ALL operators.

• Advantages:

  • Allows performing complex data retrievals.
  • Improves query readability compared to complex joins.
  • Can be used as a data source.

• Types:

  • Scalar: Returns a single value (one row and one column).
  • Multi-row: Returns one column and multiple rows. Used with IN, ANY, ALL.
  • Multi-column: Returns multiple columns. Rarely used.
  • Correlated: Depends on the outer query and is executed for each row of the outer query.

Example usage in WHERE:

SELECT
    order_id,
    customer_id,
    order_date
FROM
    orders
WHERE
    customer_id IN (
        SELECT
            customer_id
        FROM
            customers
        WHERE
            city = 'New York'
    );

Example usage in FROM:

SELECT
    customer_name,
    total_orders
FROM (
    SELECT
        c.customer_name,
        COUNT(o.order_id) AS total_orders
    FROM
        customers c
    JOIN
        orders o ON c.customer_id = o.customer_id
    GROUP BY
        c.customer_name
) AS customer_order_counts
WHERE
    total_orders > 10;