Advanced SQL Tutorial | Subqueries

9 minutesVideo LessonPDF notes

Watch Advanced SQL Tutorial | Subqueries on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Subquery Overview and Setup — Defines subqueries and introduces the sample EmployeeSalary dataset.
Subqueries in SELECT Clause — Demonstrates calculating and appending overall average salary to row-level employee data.
Window Function Comparison — Compares SELECT subquery output with AVG() OVER() and explains why GROUP BY fails here.
Subqueries in FROM Clause — Builds a derived table subquery in FROM and compares it against CTEs and temp tables.
Subqueries in WHERE Clause — Filters salary records based on age criteria from EmployeeDemographics using WHERE IN.

PDF notes

Bottom line: You will learn how to embed nested subqueries across SELECT, FROM, and WHERE clauses in SQL. You will master calculating table-wide aggregates alongside granular rows, deriving queryable inline views, and filtering datasets against secondary tables without writing full joins.

This lesson on Advanced SQL Tutorial | Subqueries is hands-on and example-driven. You will learn how to embed nested subqueries across SELECT, FROM, and WHERE clauses in SQL. You will master calculating table-wide aggregates alongside granular rows, deriving queryable inline views, and filtering datasets against secondary tables without writing full joins.

What You'll Be Able To Do

Construct scalar subqueries inside a SELECT clause to display aggregated benchmarks beside row-level details.
Evaluate trade-offs between SELECT subqueries, window functions, and GROUP BY aggregations.
Nest derived table subqueries inside a FROM clause with required aliases for multi-step data transformations.
Apply WHERE clause subqueries using the IN operator to filter records based on secondary table criteria.
Distinguish when to use WHERE subqueries versus SQL JOIN operations depending on column output needs.

Detailed Concept Walkthrough

1. Scalar Subqueries in the SELECT Clause

A scalar subquery in the SELECT clause executes an independent aggregate calculation and returns a single value to display alongside individual row data.

Mechanism: The inner query executes SELECT AVG(salary) FROM EmployeeSalary to produce a single scalar value. This scalar result is then appended as an identical constant column across every individual row returned by the outer query.
Comparison with GROUP BY: A standard GROUP BY collapses rows to the specified grouping dimensions (e.g., EmployeeID, Salary), making it impossible to compute an overall table-wide average across all rows simultaneously without losing row-level detail.
Alternative Pattern: Window functions like AVG(Salary) OVER() achieve the exact same output without requiring a separate nested query block, often with cleaner syntax and better execution plans.

-- Scalar subquery in SELECT vs Window Function equivalent
SELECT 
    EmployeeID, 
    Salary, 
    (SELECT AVG(Salary) FROM EmployeeSalary) AS AllAvgSalary
FROM EmployeeSalary;

-- Equivalent approach using window functions:
-- SELECT EmployeeID, Salary, AVG(Salary) OVER() AS AllAvgSalary FROM EmployeeSalary;

Key Takeaway: SELECT subqueries must return exactly one value per row, serving as a direct alternative to unpartitioned window functions.

2. Derived Tables in the FROM Clause

A subquery in the FROM clause acts as a temporary inline view or derived table that the outer query selects from directly.

Execution Flow: The database engine processes the inner subquery first to construct an intermediate result set in memory, and the outer query subsequently executes filters, selections, and projections on top of that intermediate dataset.
Syntax Rule: Subqueries located within the FROM clause must be assigned a table alias (e.g., FROM (...) a), allowing outer SELECT statements to explicitly reference inner column names.
Best Practice: Inline FROM subqueries cannot be reused across multiple queries and can become slow and difficult to maintain; Common Table Expressions (CTEs) or temporary tables are generally preferred for multi-step transformations.

-- Querying from a derived inline table
SELECT 
    a.EmployeeID, 
    a.AllAvgSalary
FROM (
    SELECT 
        EmployeeID, 
        Salary, 
        AVG(Salary) OVER () AS AllAvgSalary
    FROM EmployeeSalary
) a;

Key Takeaway: FROM subqueries create queryable intermediate result sets, but CTEs and temp tables offer better reusability and maintainability.

3. Membership Filtering in the WHERE Clause

A WHERE clause subquery filters the outer query's records by evaluating membership against a dynamic list generated by an inner query.

Mechanism: The inner query selects a list of keys meeting a specific condition from an auxiliary table (e.g., EmployeeID where Age > 30), and the outer query's WHERE EmployeeID IN (...) evaluates each outer row against this dynamic list.
Syntax Rule: When using the IN operator with a subquery, the inner SELECT statement must return exactly one column; selecting multiple columns or using SELECT * produces a syntax error.
JOIN vs Subquery Nuance: Use a WHERE subquery when you only need to filter rows based on another table without displaying any columns from that table; use a JOIN if you need to return columns from both tables in the final output.

-- Filter EmployeeSalary based on demographic criteria from another table
SELECT 
    EmployeeID, 
    JobTitle, 
    Salary
FROM EmployeeSalary
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM EmployeeDemographics
    WHERE Age > 30
);

Key Takeaway: WHERE IN subqueries must project a single column and are ideal for existence filtering when secondary table columns are not needed in the SELECT list.

Topics Covered in Advanced SQL Tutorial | Subqueries

Subquery Overview and Setup (0:00 - 0:45) — Defines subqueries and introduces the sample EmployeeSalary dataset.
Subqueries in SELECT Clause (0:45 - 1:40) — Demonstrates calculating and appending overall average salary to row-level employee data.
Window Function Comparison (1:40 - 2:30) — Compares SELECT subquery output with AVG() OVER() and explains why GROUP BY fails here.
Subqueries in FROM Clause (2:30 - 3:55) — Builds a derived table subquery in FROM and compares it against CTEs and temp tables.
Subqueries in WHERE Clause (3:55 - 5:30) — Filters salary records based on age criteria from EmployeeDemographics using WHERE IN.

SQL Cheat Sheet

SELECT col, (SELECT AGG(col) FROM tbl) AS alias FROM tbl — Appends an aggregate benchmark to each individual row
```
SELECT EmployeeID, (SELECT AVG(Salary) FROM EmployeeSalary) AS AvgSal FROM EmployeeSalary;
```
SELECT a.col FROM (SELECT col FROM tbl) a — Queries against an inline derived result set
```
SELECT a.EmployeeID FROM (SELECT EmployeeID, Salary FROM EmployeeSalary) a;
```

WHERE col IN (SELECT col FROM tbl WHERE condition) — Filters outer rows by matching inner query results

SELECT EmployeeID FROM EmployeeSalary WHERE EmployeeID IN (SELECT EmployeeID FROM EmployeeDemographics WHERE Age > 30);

AVG(col) OVER() — Calculates overall aggregate across all rows without grouping

SELECT EmployeeID, Salary, AVG(Salary) OVER() AS AllAvgSalary FROM EmployeeSalary;

Comparison Table

Clause Location	Output Requirement	Primary Use Case
SELECT Subquery	Single scalar value	Benchmark metric alongside row data
FROM Subquery	Tabular dataset with alias	Intermediate transformation for outer query
WHERE Subquery (IN)	Single column list	Filtering records by secondary table

Common Pitfalls

Mistake: Returning multiple columns in a WHERE IN subquery. Avoid: Select only the single matching key column in the inner query.
Mistake: Forgetting to alias a derived table inside the FROM clause. Avoid: Always assign an alias identifier immediately after the closing parenthesis.
Mistake: Using GROUP BY to display row-level values alongside table aggregates. Avoid: Use a scalar subquery or an OVER() window function instead.
Mistake: Using a subquery in WHERE when you need to display joined columns. Avoid: Switch to an explicit INNER or LEFT JOIN.

FAQs

Can I use SELECT * inside a WHERE IN subquery? No, the WHERE IN operator expects a single column of values to match against the outer column. Returning multiple columns will throw a database syntax error.
When should I prefer CTEs or temp tables over FROM subqueries? Prefer CTEs and temp tables when query logic is complex or reused across multiple steps, as FROM subqueries cannot be referenced multiple times and quickly become unreadable.
How does a SELECT subquery differ from AVG() OVER()? Both return identical overall average results alongside row data, but window functions are native analytical constructs that are typically cleaner and more performant.

Common pitfalls

Mistake: Returning multiple columns in a WHERE IN subquery. Avoid: Select only the single matching key column in the inner query.
Mistake: Forgetting to alias a derived table inside the FROM clause. Avoid: Always assign an alias identifier immediately after the closing parenthesis.
Mistake: Using GROUP BY to display row-level values alongside table aggregates. Avoid: Use a scalar subquery or an OVER() window function instead.
Mistake: Using a subquery in WHERE when you need to display joined columns. Avoid: Switch to an explicit INNER or LEFT JOIN.

What's going on, everybody? Welcome back to another sequel tutorial. Today, we are going to be talking about subqueries. Now, subqueries are often called inner queries or nested queries, and they're basically a query within a query. A subquery is used to return data that will be used in the main query or the outer query as a condition to specify the data that we want retrieved. You can use subqueries almost anywhere. You can use it in the select part of a query, the from, the where, you can also use it in insert, update and delete statements. But in today's tutorial, we're only going to be looking at the select, the from, and the where statements, and you should get a pretty good idea of how to use it in those other statements. Alright, now I'm going to paste on screen basically what we're going to be walking through today. But really quick, let's just take a look at the table that we're actually going to be working in, and that is going to be from the employee salary table, and I just want to show you the data that we're going to be working with before we actually get into it. So we have an employee ID, we have a job title, and then we have a salary. So really quick, I'm going to show you what it looks like to have a subquery in the select statement. So let's go down here really quick. And what we're going to try to do is kind of do something like a Windows function, but without actually having to do the Windows function, um, and so we're going to do this with a subquery. So I'm going to select and really quick actually let me copy this. So we're going to do employee ID. There we go. We're going to do salary, and now we can start building our subquery. So we need to do an open parenthesis, and I'm just going to copy this really quick cuz we're going to be doing it off of that table. So we're going to say select, and then I'll paste that and close it as well. But what we want to do is we want to say average and salary. Now, what this is going to do is it is literally going to run this and let's run this really quick. It is going to run this, and it's going to show that the average salary for all the employees is $47,909. So we are looking at the average salary for every employee. So when we run this, it is going to give us the employee ID, the salary, and then in the very last one, it is going to show the average salary for every employee. Now it doesn't have a column header, so, or, or column name, so let's give it, um, let's say as all average salary, and we'll run that one more time just to make it look a little prettier. Um, you can also do this in partition by. I'm going to super quickly, just really quickly write this out. Um, it should take no time at all. And then I'm going to show you why we can't do this without the subquery, why you aren't able to do this with a group by. So really quickly, let me copy this. I'm going to put it right down here, and we're going to say average salary, whoops, and we can get rid of all this. And we can say over, and we're not going to partition it by anything. So let's run both of these at the same time, and you'll see that they're the exact same outputs. And so it's just a different way of doing it in this example, but it really is just to show a comparison of how you might be able to use a subquery in the select statement. Now you might be wondering why group by does not work for this. Uh, really quickly, I'm going to write this out, and let's get rid of that. And we'll say group by, whoops, let me at least try to write it correctly, group by, and we'll do employee ID, and we also have to do salary. And then we'll say order by 1, 2. So let's run this. And as you can see, since we have to use the group by, it groups by both the ordered ID and the salary. And so we're not going to be able to get that all average salary that we're looking for, that we can get in the partition by and also the subquery in the select statement. Now I'm going to show you the subquery in the from statement. So let's just get rid of that really quick, and let's say select everything. Let's say from, and we're going to do an open parenthesis here, and here is where we're going to write our subquery. So if you have watched previous videos where I've done uh tutorials on the CTE or tutorial on the temp tables, this is one that is very much like those, except I think a little bit less efficient. When I'm doing something where I'm creating a table and then querying off of it, which is what we're about to do, I much prefer a CTE or a temp table. Subqueries tend to be a little bit slow compared to a temp table or a CTE. I tend to use temp tables a lot more cuz you can reuse them over and over, whereas a subquery, you cannot. You have to write it out each time. So really quickly, I'm going to show you how it's done, although I don't really recommend using this method. Really quickly, let's go up here, and let's steal this partition by really quick. This will be our subquery. Uh and let's paste this in here. I'm going to make this look a little nicer just so you can visualize it a little bit easier. Um, so really quick, what this is going to do is it is first going to run this and create this table, again, much like a temp table or a CTE. So let's execute this really quick. It's going to create this table, and then it's going to allow us to query off of it. So I can actually say, um, and let me give kind of an alias to this, a.employeeID, and then let's say all average salary. So now I can take um columns from this inner query, if I want to, and just select those, or I can select everything and return that entire table. Again, I much prefer a temp table or a CTE for this type of situation, but as an example, I just wanted to show you how it works. Now let's go down to the subquery in the where statement, but really quick, I just want to steal this query so that I don't have to rewrite everything. And let's get rid of this really quick and add back the job title. All right. Let's look at this really quick. So we have our table that we've been using, our employee ID, job title, salary. So for this example, we only want to return employees if they're over the age of 30. And as you can see in this table, there is no age column. That is in the employee demographics table. Now if we wanted, we could join to that table and get that information, or we could use a subquery. And so for this example, we are going to be using a subquery. So let's go right down here and say where employee ID is in, and we'll do an open parenthesis, and now this is where we're going to build out the subquery. So, just for visual purposes, I'm going to go right here. I'm going to say select everything, and we'll do from employee demographics and close the parentheses. So we're going to try to select something in this subquery that will then identify the employee IDs that are over the age of 30. So really quickly, let's take a look at this table. So right now we have the entire table selected. So we have the employee ID, first name, last name, age, and gender. So in this subquery, the only thing that should be returned is the employee ID. And in fact, in your subquery, you can only have one column selected. So I can't select everything. I have to specify one column. And that's a little bit different than how we did it in this from statement, where we were basically able to select the entire table, and then in the select statement specify what columns we wanted. In the where statement, we can't do that. So we want to return the employee ID, and we also want to say where the age is greater than 30. So let's run this really quick and see if it works. As you can see in the results, these are the employees who are over the age of 30. Now, if you wanted to display the age as a column in this output, you would have to join to that table and then put that column or that field in the select statement. But in a lot of situations, you won't actually want or need to do that, and so a subquery can be a really good option in these scenarios. With that being said, this is the last video in the advanced SQL tutorials. I hope that this series has been helpful and that you've learned something along the way. Thank you so much for joining me. I really appreciate it. If you like this video, be sure to like and subscribe below, and I'll see you in the next video.

Frequently asked questions

Can I use SELECT * inside a WHERE IN subquery?

No, the WHERE IN operator expects a single column of values to match against the outer column. Returning multiple columns will throw a database syntax error.

When should I prefer CTEs or temp tables over FROM subqueries?

Prefer CTEs and temp tables when query logic is complex or reused across multiple steps, as FROM subqueries cannot be referenced multiple times and quickly become unreadable.

How does a SELECT subquery differ from AVG() OVER()?

Both return identical overall average results alongside row data, but window functions are native analytical constructs that are typically cleaner and more performant.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.