SQL Subqueries Explained Simply (With Practical Examples)

If you’re learning SQL, subqueries can feel confusing at first. I remember when I was starting out, the word subquery sounded more complicated than it actually is.

But here’s the simple truth:

A subquery is just a query inside another query.

That’s it.

Let’s break it down properly so you truly understand it.

What Is a Subquery in SQL?

A subquery (also called a nested query) is a SQL query written inside another SQL query.

It usually sits inside:

• SELECT
• WHERE
• FROM
• HAVING

Think of it like this:

The inner query runs first
The outer query uses the result

Simple Example of a Subquery

Imagine you have a table called employees.

You want to find employees who earn more than the average salary.

Here’s how you write it:

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

What’s happening here?

1. The subquery calculates the average salary.
2. The outer query selects employees earning more than that average.

This is one of the most common SQL subquery examples asked in interviews.

Types of SQL Subqueries

Let’s simplify the main types.

Single-Row Subquery

Returns only one value.

Example:

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

Since AVG() returns one value, this is a single-row subquery.

Multiple-Row Subquery

Returns multiple values.

Example:

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'London'
);

Here, the subquery may return multiple department IDs.

Correlated Subquery

This is slightly more advanced.

A correlated subquery depends on the outer query and runs once for each row.

Example:

SELECT e1.name, e1.salary
FROM employees e1
WHERE salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

This compares each employee’s salary to the average salary in their department.

In interviews, you’ll often hear:

Explain correlated vs non-correlated subquery.

Now you know the difference:

• Non-correlated → runs once
• Correlated → runs per row

Subquery vs JOIN: Which Is Better?

This is another common SQL interview question.

There is no “better” option.

• Use subqueries when the logic feels clearer.
• Use JOINs when combining multiple tables for performance.

In real-world projects, many professionals prefer JOINs for better readability and optimization especially in tools like Microsoft Power BI or Tableau, where query performance matters.

Where Subqueries Are Commonly Used

You’ll often use subqueries to:

• Compare values against averages
• Filter based on aggregated data
• Find top or bottom performers
• Check existence using EXISTS
• Solve interview case scenarios

If you’re preparing for entry-level roles, understanding subqueries is extremely important. They frequently appear in technical interviews.

Common Beginner Mistakes

Here are mistakes I see often:

Forgetting parentheses
Using = instead of IN when multiple rows are returned
Writing slow correlated subqueries unnecessarily
Not understanding execution order

Always remember:

The inner query runs first.

SQL subqueries are not scary. They are just queries inside queries.

Once you understand how they execute, you’ll start using them naturally in real projects and interviews.

If you’re building your SQL foundation, mastering subqueries is non-negotiable. It separates beginners from confident data analysts.

Keep practicing, test different examples, and most importantly you should understand what your query is doing, not just copying syntax.

FAQs