How to Write Simple Subqueries in SQL?

How to Write Simple Subqueries in SQL? Complete Guide

Subqueries in SQL are powerful tools that allow you to nest one query inside another to fetch data based on the results of the inner query. They are commonly used for filtering, aggregation, and performing calculations. In this guide, we’ll explore how to write simple subqueries effectively, types of subqueries, and practical examples.

1. What is a Subquery in SQL?

A subquery (or inner query) is a query that is embedded inside another SQL query (outer query). It returns data that the outer query can use for further processing.

Key Features of Subqueries:

Can be used with SELECT, INSERT, UPDATE, and DELETE statements.
Enclosed within parentheses.
Typically used in WHERE, HAVING, and FROM clauses.

2. Types of Subqueries in SQL

Subquery Type	Description	Example Use Case
Single-row Subquery	Returns one row of results.	Finding the employee with the highest salary.
Multi-row Subquery	Returns multiple rows of results.	Listing employees in specific departments.
Correlated Subquery	Refers to columns in the outer query.	Fetching latest order for each customer.
Nested Subquery	Contains another subquery inside it.	Advanced filtering and data aggregation.

3. Writing Simple Subqueries in SQL

a) Single-row Subquery Example

Problem: Find employees with a salary higher than the average salary.

SQL Query:

sqlCopyEditSELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Explanation:

Inner query: (SELECT AVG(Salary) FROM Employees) calculates the average salary.
Outer query: Fetches employees whose salary is higher than the average.

b) Multi-row Subquery Example

Problem: Find employees who work in the same department as ‘John’.

SQL Query:

sqlCopyEditSELECT Name, Department
FROM Employees
WHERE Department IN (SELECT Department FROM Employees WHERE Name = 'John');

Explanation:

Inner query: Finds the department of ‘John’.
Outer query: Lists all employees in that department.

c) Correlated Subquery Example

Problem: Find employees who earn more than the average salary in their department.

SQL Query:

sqlCopyEditSELECT E1.Name, E1.Salary, E1.Department
FROM Employees E1
WHERE E1.Salary > (
    SELECT AVG(E2.Salary)
    FROM Employees E2
    WHERE E2.Department = E1.Department
);

Explanation:

The inner query depends on the outer query’s department column.
Compares each employee’s salary to the average salary of their department.

d) Nested Subquery Example

Problem: Find the name of employees who joined after the oldest employee in the ‘IT’ department.

SQL Query:

sqlCopyEditSELECT Name
FROM Employees
WHERE JoinDate > (
    SELECT MIN(JoinDate)
    FROM Employees
    WHERE Department = 'IT'
);

Explanation:

Inner subquery: Gets the earliest join date in the ‘IT’ department.
Outer query: Fetches employees who joined later.

4. Common Uses of Subqueries in SQL

Use Case	Example
Filtering Data	`WHERE column IN (subquery)` to filter results.
Performing Calculations	Using subqueries for aggregate functions like AVG, MAX, MIN.
Updating Data	`UPDATE` with subquery to modify specific rows based on conditions.
Inserting Data	`INSERT INTO` with a subquery to add filtered data from another table.

5. Best Practices for Using Subqueries

✅ Use EXISTS instead of IN: For better performance in large datasets.
✅ Limit correlated subqueries: They can cause performance issues due to repeated execution.
✅ Prefer JOINs for multiple rows: JOINs are generally more efficient than subqueries returning multiple rows.
✅ Optimize with Indexes: Index columns used in subqueries to speed up execution.

6. Subquery vs. JOIN: Key Differences

Feature	Subquery	JOIN
Execution	Inner query runs first, followed by the outer query.	Combines rows from two or more tables based on a related column.
Performance	Slower for multi-row results.	Faster for large datasets with proper indexing.
Use Case	Ideal for single-row results or filtering.	Best for combining data from multiple tables.
Readability	Easier to read for simple filtering tasks.	Can become complex with multiple tables.

7. Limitations of Subqueries

Performance: Can slow down execution, especially correlated subqueries.
Complexity: Nested subqueries can reduce readability.
Not Supported Everywhere: Some databases have limitations on subquery usage.

8. Practical Example: Using Subqueries

Problem: Find the second-highest salary in the Employees table.

SQL Query:

sqlCopyEditSELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Explanation:

Inner query: Gets the highest salary.
Outer query: Fetches the maximum salary below the highest.

9. Subquery Performance Tips

🔄 Use EXISTS: More efficient than IN for checking existence.
📊 Index subquery columns: Reduces execution time.
🛠️ Rewrite as JOIN: Consider using JOINs for multi-row subqueries.
📉 Avoid correlated subqueries: Replace with JOINs when possible.

10. Final Thoughts

Subqueries in SQL are essential for complex data retrieval tasks and help simplify SQL queries for specific scenarios. By understanding different types of subqueries and following best practices, you can write efficient and optimized SQL queries.

For more SQL tips, visit our SQL Community.
Check out this detailed guide on SQL Subqueries for further learning.