SQL Subquery Example: Master Nested Queries with Real-Life Use Cases

SQL Subquery is a nifty tool in your programming toolkit that’s worth getting to grips with. It allows you to layer your queries like a lovely lasagna, making complex data retrieval a walk in the park. Curious about how SQL Subquery can transform your database manipulation skills? Then you’re in the right place! Stick around, and I’ll take you through the essentials, breaking down the jargon and showing you some practical examples. By the end, you’ll be handling subqueries like a pro!

What is a Subquery in SQL?

A subquery is a SQL query written inside another query. It’s like a “query within a query” that helps you get results based on dynamic conditions. The inner query runs first and passes its result to the outer query.

Example:

SELECT name FROM students WHERE id IN (SELECT student_id FROM results WHERE marks > 80);

Here, the part in parentheses is the subquery. It fetches IDs of students who scored more than 80, and the outer query uses those IDs to get their names.

Where Subqueries are Commonly Used:

• In the WHERE clause: To filter results dynamically
• In the FROM clause: To create temporary tables
• In the SELECT clause: To fetch calculated or aggregated values
• In UPDATE/DELETE statements: To act on specific rows based on subquery results

Difference Between Subquery and JOIN (Brief):

Feature	Subquery	JOIN
Purpose	Filters or derives data	Combines data from multiple tables
Readability	Easier for single-table focus	Better for combining multiple tables
Performance	May be slower with large data	Often faster due to direct linking
Usage	Used inside SELECT, WHERE, FROM	Used in FROM with ON clause

• Subquery: Good for step-by-step filtering
• JOIN: Best when you need to bring together data from related tables

Types of SQL Subqueries

1. Scalar Subquery:

Returns only one value (single row, single column).
Example:

SELECT name, (SELECT MAX(marks) FROM results) AS highest_marks FROM students;

2. Correlated Subquery:

Depends on the outer query and runs once for each row in the outer query.
Example:

SELECT name FROM students s WHERE marks > (SELECT AVG(marks) FROM results r WHERE r.class = s.class);

3. Nested Subquery (or Simple Subquery):

A basic subquery that is independent and runs first, passing its result to the main query.
Example:

SELECT name FROM students WHERE id IN (SELECT student_id FROM results WHERE subject = 'Math');

4. Inline View (Subquery in FROM clause):

A subquery treated like a table inside the FROM clause.
Example:

SELECT avg_marks FROM (SELECT AVG(marks) AS avg_marks FROM results) AS temp;

SQL Subquery in SELECT Clause (with Example)

Explanation:

A subquery in the SELECT clause is used to return a single value that can be included in the final output. It often calculates an aggregate like average, total, or maximum.

SQL Code Example:

SELECT 
  name, 
  (SELECT MAX(marks) FROM results) AS highest_marks 
FROM 
  students;

This query retrieves the name of every student and displays the highest marks from the results table next to each name.

Output Explanation:

Assume results table has highest marks as 98.
Then, the output would look like:

name	highest_marks
Alice	98
Bob	98
Carol	98

The subquery runs once, fetches the maximum marks, and adds that value to each row in the output.

SQL Subquery in WHERE Clause (with Example)

Explanation:

A subquery in the WHERE clause filters results based on conditions dynamically fetched from another table. It helps when the condition depends on values from another query.

SQL Code Example:

SELECT 
  name 
FROM 
  students 
WHERE 
  id IN (SELECT student_id FROM results WHERE marks > 80);

This query returns the names of students who scored more than 80 marks, based on the results table.

Output Explanation:

Suppose in results table, student IDs 2 and 3 scored above 80.
If students table has:

id	name
1	Alice
2	Bob
3	Carol

Then the output will be:

name
Bob
Carol

The subquery first finds student IDs with marks > 80, and the outer query shows names matching those IDs.

SQL Subquery in FROM Clause (with Example)

Explanation:

A subquery in the FROM clause is treated like a temporary table or view. This is useful when you want to perform calculations or filtering first and then use that result in the main query.

It’s often called an inline view.

SQL Code Example:

SELECT 
  department, 
  avg_marks 
FROM 
  (SELECT department, AVG(marks) AS avg_marks FROM results GROUP BY department) AS dept_avg;

Output Explanation:

If the results table has:

student_id	department	marks
1	IT	85
2	IT	90
3	HR	75
4	HR	80

The subquery:

SELECT department, AVG(marks) AS avg_marks FROM results GROUP BY department

Produces:

department	avg_marks
IT	87.5
HR	77.5

The outer query simply selects from this result, giving the final output:

department	avg_marks
IT	87.5
HR	77.5

Use Cases and Benefits of SQL Subqueries

1. Filtering Data with Conditions:

Subqueries make filtering dynamic. Instead of hardcoding values, you can filter based on data from other tables or calculations.

Example:

SELECT name FROM students WHERE id IN (SELECT student_id FROM results WHERE marks > 80);

Benefit: You get only those rows that meet conditions derived from another dataset—ideal for real-time filtering.

2. Replacing Temporary Tables:

You can use subqueries in the FROM clause instead of creating a separate temporary table.

Example:

SELECT department, avg_marks 
FROM (SELECT department, AVG(marks) AS avg_marks FROM results GROUP BY department) AS dept_avg;

Benefit: Saves time and keeps the query self-contained—no need for manual table creation or clean-up.

3. Handling Complex Business Logic in a Single Query:

Subqueries allow you to handle layered logic like “Find employees earning more than the average in their department” in one go.

Example:

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

Benefit: Reduces multiple steps into one efficient, readable query—ideal for dashboards and reports.

Common Mistakes to Avoid in SQL Subqueries

1. Performance Issues:

Running subqueries inside loops or on large datasets (especially correlated subqueries) can slow down queries drastically.

Avoid by:

• Using JOINs where applicable
• Ensuring proper indexing
• Limiting data returned by subqueries

2. Misunderstanding Correlated Subqueries:

Many confuse correlated subqueries with regular ones. Correlated subqueries depend on each row of the outer query, which can be inefficient.

Mistake Example:

SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees);

This is not a correlated subquery. But if you add WHERE department = e.department, it becomes one—and runs per row.

Avoid by:

• Understanding execution flow
• Optimizing or converting to JOINs if needed

3. Not Using Aliases Properly:

When using subqueries in the FROM clause, missing an alias causes errors.

Wrong:

SELECT * FROM (SELECT * FROM employees);

Correct:

SELECT * FROM (SELECT * FROM employees) AS emp_sub;

Avoid by:

• Always assigning an alias to subqueries, especially in the FROM clause

Interview Questions on SQL Subquery

Conceptual Questions:

1. What is a subquery in SQL?
   A subquery is a query inside another query, used to fetch results dynamically based on another result set.
2. What is the difference between a correlated and a non-correlated subquery?
   A correlated subquery refers to a column from the outer query and runs once for each outer row; a non-correlated one runs independently.
3. Can subqueries be used in INSERT, UPDATE, or DELETE statements?
   Yes. Subqueries can provide the values or filters for these statements.

Practical Questions:

1. Write a query to find employees who earn more than the average salary.

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

2. Write a query to find departments where the average salary is above 70,000.

SELECT department 
FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg 
WHERE avg_salary > 70000;

With our AI-powered SQL online compiler, users can instantly write, run, and test their code effortlessly. The AI-enabled system simplifies the entire process, making it faster and more efficient to work on SQL queries without any installation. This revolutionary tool guarantees quick and reliable results.

Conclusion

SQL Subquery offers immense benefits, improving data retrieval skills and boosting overall database knowledge. Give it a try—it’s a rewarding challenge that sharpens problem-solving abilities. Ready to dive deeper into programming? Check out Newtum for more resources and expand your coding horizons with languages like Java, Python, C, and C++.

Edited and Compiled by

This article was compiled and edited by @rasikadeshpande, who has over 4 years of experience in writing. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author