Why subqueries are needed in SQL

Introduction

Subqueries help you ask a question inside another question in a database. They let you find data step-by-step, making complex searches easier.

When you want to find records based on a calculation or condition from another table.

When you need to filter data using results from a different query.

When you want to compare each row to a set of values found by another query.

When you want to organize your query into smaller parts for clarity.

When you want to avoid repeating the same logic multiple times.

Syntax

SQL

SELECT column_name(s) FROM table_name WHERE column_name operator (SELECT column_name FROM another_table WHERE condition);

The subquery is placed inside parentheses and runs first.

The main query uses the result of the subquery to filter or compare data.

Examples

Finds employees who work in the Sales department by first finding the Sales department's id.

SQL

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

Finds products priced higher than the average price of all products.

SQL

SELECT product_name FROM products WHERE price > (SELECT AVG(price) FROM products);

Finds customers who placed orders after January 1, 2024.

SQL

SELECT customer_name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE order_date > '2024-01-01');

Sample Program

This query finds all employees who work in the Marketing department by first finding the department's id.

SQL

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Marketing');

Important Notes

Subqueries can return one value or multiple values depending on the query.

Using subqueries can make your SQL easier to read and maintain.

Sometimes, subqueries can be slower than joins, so test performance if needed.

Summary

Subqueries let you use one query inside another to break down complex questions.

They help filter or compare data based on results from other queries.

Using subqueries makes your SQL clearer and more organized.