Basic Subqueries and Their Use Cases in SQL

Table of Contents

1. Introduction
2. What is a Subquery?
3. Types of Subqueries
4. Why Use Subqueries?
5. Syntax of a Subquery
6. Subqueries in SELECT Clause
7. Subqueries in FROM Clause
8. Subqueries in WHERE Clause
9. Single-Row vs Multi-Row Subqueries
10. Using Subqueries with IN
11. Using Subqueries with =, >, <, !=
12. Using Subqueries with EXISTS
13. Correlated vs Non-Correlated Subqueries
14. Real-World Example: Top-Selling Products
15. Real-World Example: Customers Without Orders
16. Subquery vs JOIN: When to Use What
17. Performance Considerations
18. Common Errors in Subqueries
19. Best Practices for Subqueries
20. Summary and What’s Next

1. Introduction

SQL subqueries allow you to nest a query inside another query. This helps break complex logic into manageable pieces, supports modular querying, and powers dynamic filtering.

2. What is a Subquery?

A subquery is a query that is embedded inside another SQL statement. It is enclosed in parentheses and can return a single value or a set of values.

3. Types of Subqueries

4. Why Use Subqueries?

• To avoid temporary tables
• To write readable nested logic
• To dynamically filter based on other data
• To create reusable logic inside complex queries

5. Syntax of a Subquery

sqlCopyEditSELECT column
FROM table
WHERE column IN (
    SELECT column FROM another_table WHERE condition
);

6. Subqueries in SELECT Clause

sqlCopyEditSELECT name,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Each row shows the employee’s name and the average salary across all employees.

7. Subqueries in FROM Clause

sqlCopyEditSELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_summary
WHERE avg_salary > 60000;

Used when you want to treat the result of one query as a virtual table.

8. Subqueries in WHERE Clause

sqlCopyEditSELECT * FROM products
WHERE price > (
    SELECT AVG(price) FROM products
);

This filters products that cost more than the average price.

9. Single-Row vs Multi-Row Subqueries

• Single-row: use =, >, <, !=
• Multi-row: use IN, ANY, ALL, EXISTS

Example:

sqlCopyEditSELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders WHERE total > 1000
);

10. Using Subqueries with IN

sqlCopyEditSELECT name FROM users
WHERE id IN (
    SELECT user_id FROM logins WHERE login_date >= '2024-01-01'
);

Returns users who logged in recently.

11. Using Subqueries with =, >, <, !=

sqlCopyEditSELECT name, salary FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Useful for value comparisons against aggregates or single-row results.

12. Using Subqueries with EXISTS

sqlCopyEditSELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Checks for the existence of related rows — often more efficient than IN.

13. Correlated vs Non-Correlated Subqueries

• Non-Correlated: Executed once
• Correlated: Re-evaluated per row of the outer query

Correlated Example:

sqlCopyEditSELECT name FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

Each employee is compared to their department’s average.

14. Real-World Example: Top-Selling Products

sqlCopyEditSELECT * FROM products
WHERE id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    HAVING SUM(quantity) = (
        SELECT MAX(total_quantity)
        FROM (
            SELECT product_id, SUM(quantity) AS total_quantity
            FROM order_items
            GROUP BY product_id
        ) AS totals
    )
);

Finds products with the highest overall sales.

15. Real-World Example: Customers Without Orders

sqlCopyEditSELECT name FROM customers
WHERE id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

Identifies inactive customers.

16. Subquery vs JOIN: When to Use What

17. Performance Considerations

• Subqueries inside SELECT can be slow if not scalar or optimized
• Correlated subqueries run once per row — use with caution
• Index columns used in subquery filters (IN, EXISTS)
• Try converting correlated subqueries to joins for large datasets

18. Common Errors in Subqueries

19. Best Practices for Subqueries

• Use aliases to make subqueries readable
• Avoid deeply nested subqueries if possible
• Cache subquery results (e.g., CTEs in PostgreSQL)
• Always test subqueries independently before integrating

20. Summary and What’s Next

Subqueries help you write modular, dynamic, and powerful SQL. From filtering with nested conditions to treating subquery results as tables, they unlock advanced capabilities without making your queries overly complex.