SQL - Sub Queries

SQL Subqueries

What is a Subquery?

A subquery, also known as an inner query or nested query, is a SELECT query that is nested inside another SQL query. Subqueries allow you to construct more complex and powerful queries by using the result of one query as an input for another.

They can be used in the SELECT, FROM, WHERE, and HAVING clauses of the main (outer) query, often in conjunction with operators like IN, NOT IN, EXISTS, ANY, and ALL.

Key Guidelines for Subqueries

• A subquery must always be enclosed in parentheses ().
• A subquery can return a single value (scalar), a single column of multiple rows, or a full table.
• An ORDER BY clause is generally not allowed in a subquery, unless you are also using a TOP or LIMIT clause.
• For readability, especially with nested subqueries, consider using Common Table Expressions (CTEs) as a modern alternative.

Subqueries in a WHERE Clause

The most common use of subqueries is to filter results in a WHERE clause. The subquery is executed first, and its result is used by the outer query.

Let’s use our CUSTOMERS table for the examples. (See previous chapter for CREATE and INSERT statements).

SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE ID IN (SELECT CUSTOMER_ID FROM ORDERS WHERE AMOUNT > 2000);

This query first finds all CUSTOMER_IDs from the ORDERS table who have placed an order with an amount greater than 2000. Then, the outer query selects the details of those customers from the CUSTOMERS table.

Modern Alternative: Common Table Expressions (CTEs)

For complex or nested subqueries, modern SQL offers a much more readable and maintainable alternative: Common Table Expressions (CTEs), defined using the WITH clause. A CTE allows you to define a temporary, named result set that you can reference within your main query.

Here is the previous example rewritten using a CTE:

WITH HighValueCustomers AS (
    -- First, define the set of customer IDs with high-value orders
    SELECT CUSTOMER_ID
    FROM ORDERS
    WHERE AMOUNT > 2000
)
-- Now, use this named result set in the main query
SELECT c.ID, c.NAME, c.SALARY
FROM CUSTOMERS c
JOIN HighValueCustomers hvc ON c.ID = hvc.CUSTOMER_ID;

Notice how the logic is separated into clear, sequential steps, making the query much easier to understand and debug.

Subqueries with DML Statements

Subqueries with INSERT

Subqueries can be used to populate a table with data from another table.

-- Assuming CUSTOMERS_BKP is an empty table with the same structure as CUSTOMERS
INSERT INTO CUSTOMERS_BKP (ID, NAME, AGE, CITY, SALARY)
SELECT ID, NAME, AGE, CITY, SALARY
FROM CUSTOMERS
WHERE AGE > 30;

Subqueries with UPDATE

You can use a subquery to determine which rows to update.

-- Give a 10% salary increase to customers from Mumbai
UPDATE CUSTOMERS
SET SALARY = SALARY * 1.10
WHERE CITY = 'Mumbai';

A more complex example using a subquery:

-- Give a 5% bonus to customers who have placed an order
UPDATE CUSTOMERS
SET SALARY = SALARY * 1.05
WHERE ID IN (SELECT DISTINCT CUSTOMER_ID FROM ORDERS);

Subqueries with DELETE

A subquery can identify rows to be deleted.

-- Delete customers with no orders
DELETE FROM CUSTOMERS
WHERE ID NOT IN (SELECT CUSTOMER_ID FROM ORDERS);

Performance & Pitfalls

Correlated Subqueries

A correlated subquery is one that depends on the outer query for its values. It is evaluated once for each row processed by the outer query, which can lead to poor performance. Often, a correlated subquery can be rewritten as a JOIN for better efficiency.

Common Pitfall: Scalar Subquery Error

If a subquery is used in a place that expects a single value (e.g., WHERE column = (subquery)), you will get an error if the subquery returns more than one row. In such cases, you likely need to use IN instead of = or reconsider your logic.