Lesson 30: Subqueries (Nested Queries)

A Subquery (or Inner Query, Nested Query) is a SELECT statement embedded within another SQL query. They are used to perform operations that rely on the result of another query.

Where Subqueries are Used

Subqueries can be used in several places:

1. In the WHERE clause (most common): To filter the outer query.
2. In the SELECT clause: To return a single, scalar value (scalar subquery).
3. In the FROM clause: To treat the result of the subquery as a temporary table (Derived Table).

Example: Subquery in the WHERE Clause

Find all products whose price is greater than the average price of all products.

• Step 1 (Inner Query): Find the average price.
• Step 2 (Outer Query): Use that average to filter products.

sql SELECT Name, Price FROM Products WHERE Price > ( SELECT AVG(Price) FROM Products -- Inner Query );

Using IN with Subqueries

If the inner query returns a list of values (more than one row), you must use the IN operator.

Example: Find Employees in Specific Departments

Find all employees who work in departments located in 'New York'.

sql SELECT Name FROM Employees WHERE DeptID IN ( SELECT DeptID FROM Departments WHERE Location = 'New York' );