SQL Subqueries

A Subquery is a query nested inside another SQL query. Subqueries allow you to perform operations that depend on the result of another query. They are powerful for filtering, comparing, and calculating derived values.

📌 Syntax

-- Basic Subquery in WHERE clause
SELECT column1, column2
FROM table_name
WHERE column3 operator (SELECT column FROM another_table WHERE condition);

-- Subquery in FROM clause
SELECT avg_salary
FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS derived_table;

-- Subquery in SELECT clause
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

📊 Example Table: users

id | name    | age | city
---+---------+-----+---------
1  | Alice   | 22  | New York
2  | Bob     | 30  | London
3  | Charlie | 18  | Sydney
4  | Diana   | 27  | Toronto
5  | Ethan   | 35  | Paris

🔍 Example 1: Subquery in WHERE

Get users whose age is above the average age:

SELECT name, age 
FROM users 
WHERE age > (SELECT AVG(age) FROM users);

🔍 Example 2: Subquery in FROM

Calculate the average age of users:

SELECT avg_age
FROM (SELECT AVG(age) AS avg_age FROM users) AS derived_table;

🔍 Example 3: Subquery in SELECT

Count orders for each user:

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

🔍 Example 4: Subquery with IN

Find users who have placed orders for a specific product:

SELECT name 
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE product = 'Laptop');

⚡ Best Practices

• Use subqueries to break complex problems into smaller, manageable queries.
• Ensure subqueries return only one column when used with IN or WHERE.
• Consider using JOINs if the subquery can be rewritten for better performance.
• Always alias derived tables to avoid syntax errors.

📝 Summary

SQL Subqueries provide a way to embed queries within queries, enabling complex data filtering, calculations, and comparisons. They can be used in WHERE, FROM, SELECT clauses, and combined with operators like IN, EXISTS, or ANY to build advanced queries.

🚀 Next Steps

Next, we will explore SQL EXISTS, a related operator that tests for the existence of rows returned by a subquery.