Subqueries in MySQL | Intermediate MySQL

11 minutesVideo LessonPDF notes

Watch Subqueries in MySQL | Intermediate MySQL 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 Fundamentals — An introduction defines subqueries as nested queries and outlines their usage in WHERE, SELECT, and FROM clauses.
WHERE Clause Subqueries — A nested query filters demographic records using department IDs retrieved from the salary table.
Operand Column Error — Demonstrating a multi-column subquery reveals the single-column constraint required by comparison operands.
SELECT Clause Subqueries — A scalar subquery appends an overall average salary next to individual employee salaries without GROUP BY.
FROM Clause Derived Tables — Nesting an aggregated query inside a FROM clause illustrates derived tables and the mandatory alias rule.
Derived Column Aliasing — Resolving unknown column errors highlights the difference between backtick escaping and explicit column aliasing.

PDF notes

Bottom line: You will write and integrate subqueries across WHERE, SELECT, and FROM clauses in MySQL to solve multi-step data retrieval problems. You will be able to filter records against dynamic result sets, compute row-level benchmark metrics without collapsing your dataset, and query derived tables using required aliases.

This lesson on Subqueries in MySQL | Intermediate MySQL is hands-on and example-driven. You will write and integrate subqueries across WHERE, SELECT, and FROM clauses in MySQL to solve multi-step data retrieval problems. You will be able to filter records against dynamic result sets, compute row-level benchmark metrics without collapsing your dataset, and query derived tables using required aliases.

What You'll Be Able To Do

Construct WHERE clause subqueries using IN to filter datasets against dynamic lookup lists
Embed scalar subqueries in SELECT statements to compare individual row values against aggregate benchmarks
Build derived tables in the FROM clause to execute multi-level aggregations on summarized data
Apply required table and column aliases to prevent syntax errors in derived table operations
Diagnose operand and unknown column errors caused by mismatched column counts or unaliased aggregated fields

Detailed Concept Walkthrough

1. WHERE Clause Subqueries

A subquery in a WHERE clause evaluates an inner query first to produce a dynamic value or list against which outer table records are filtered.

Mechanism: The inner query executes independently to produce a single-column list of values, and the outer query uses operators like IN to check for membership against each row.
Under the Hood: MySQL evaluates the inner query to construct an intermediate set in memory, then scans the outer table row by row, checking if the outer column value matches any element in that set.
Syntax Rule: When using standard membership operators like IN or equality comparisons, the subquery SELECT statement must project exactly one column to avoid operand mismatch errors.
Best Practice: Use a WHERE subquery as an alternative to an INNER JOIN when you only need to filter outer records based on the existence of attributes in a related table without pulling in extra columns.

-- Filter demographics to employees working in department 1
SELECT *
FROM employee_demographics
WHERE employee_id IN (
    SELECT employee_id
    FROM employee_salary
    WHERE dept_id = 1
);

Key Takeaway: Inner queries in a WHERE clause with IN must return exactly one column to satisfy MySQL operand constraints.

2. SELECT Clause Scalar Subqueries

A scalar subquery in the SELECT list calculates a single global aggregate value that repeats across every row of the outer query without collapsing the outer dataset.

Mechanism: The subquery computes a single scalar value, such as a whole-table AVG, and appends it as an independent computed column alongside each outer table record.
Execution Flow: Unlike adding an aggregate function directly into a standard SELECT, a scalar subquery bypasses the requirement for an outer GROUP BY clause, preventing the outer rows from collapsing into summary groups.
Under the Hood: The database evaluates the subquery to compute the aggregate metric once and distributes that single scalar constant across the returned rows of the outer query stream.
Best Practice: Always alias scalar subquery columns in the SELECT clause to provide clean, readable header names for downstream reporting and analytics.

-- Compare individual salaries against the company-wide average
SELECT 
    first_name,
    salary,
    (
        SELECT AVG(salary)
        FROM employee_salary
    ) AS average_salary
FROM employee_salary;

Key Takeaway: Scalar subqueries in a SELECT clause allow row-level fields and table-wide aggregates to coexist without collapsing rows via GROUP BY.

3. FROM Clause Derived Tables

A derived table is a subquery in the FROM clause that functions as a temporary in-line table, allowing secondary aggregations and transformations on top of pre-summarized results.

Mechanism: The inner query executes a grouped aggregation, and the outer query treats that summarized result set as if it were a physical database table to run subsequent calculations.
Syntax Rule: MySQL strictly requires every derived table in the FROM clause to have an explicit table alias assigned using the AS keyword or a trailing identifier.
Under the Hood: MySQL materializes the inner query result into an anonymous temporary table structure in memory before the outer query's SELECT, WHERE, and GROUP BY operations execute.
Best Practice: Explicitly alias all calculated and aggregated columns within the inner query (e.g., AS max_age) to avoid unwieldy backtick referencing like MAX(age) in outer calculations.

-- Calculate the average of aggregated group metrics across genders
SELECT 
    AVG(max_age) AS avg_max_age,
    AVG(min_age) AS avg_min_age
FROM (
    SELECT 
        gender,
        AVG(age) AS avg_age,
        MAX(age) AS max_age,
        MIN(age) AS min_age,
        COUNT(age) AS count_age
    FROM employee_demographics
    GROUP BY gender
) AS aggregated_table;

Key Takeaway: Derived tables must always be aliased and should expose explicit column aliases for clean multi-level aggregations.

Topics Covered in Subqueries in MySQL | Intermediate MySQL

Subquery Fundamentals (0:00 - 0:45) — An introduction defines subqueries as nested queries and outlines their usage in WHERE, SELECT, and FROM clauses.
WHERE Clause Subqueries (0:45 - 1:55) — A nested query filters demographic records using department IDs retrieved from the salary table.
Operand Column Error (1:55 - 2:30) — Demonstrating a multi-column subquery reveals the single-column constraint required by comparison operands.
SELECT Clause Subqueries (2:30 - 4:00) — A scalar subquery appends an overall average salary next to individual employee salaries without GROUP BY.
FROM Clause Derived Tables (4:00 - 5:30) — Nesting an aggregated query inside a FROM clause illustrates derived tables and the mandatory alias rule.
Derived Column Aliasing (5:30 - 7:15) — Resolving unknown column errors highlights the difference between backtick escaping and explicit column aliasing.

SQL Cheat Sheet

WHERE col IN (SELECT col FROM table) — Filters outer rows matching values returned by inner query
```
SELECT * FROM emp WHERE id IN (SELECT id FROM dept_emp WHERE dept_id = 1);
```

SELECT col, (SELECT AVG(col) FROM table) AS alias — Appends a single scalar metric across all returned rows

SELECT first_name, salary, (SELECT AVG(salary) FROM employee_salary) AS avg_sal FROM employee_salary;

FROM (SELECT ... GROUP BY ...) AS derived_alias — Queries an in-line aggregated result set as a table

SELECT AVG(max_age) FROM (SELECT MAX(age) AS max_age FROM employee_demographics GROUP BY gender) AS dt;

aggregation_expression — Escapes unaliased derived column names containing parentheses or spaces

SELECT AVG(`MAX(age)`) FROM (SELECT MAX(age) FROM employee_demographics GROUP BY gender) AS dt;

Comparison Table

Subquery Placement	Output Requirement	Primary Use Case
WHERE Clause	Single column list or value	Dynamic row filtering
SELECT Clause	Single scalar value	Row-level benchmark comparisons
FROM Clause	Tabular dataset with alias	Multi-level summarized aggregations

Common Pitfalls

Mistake: Returning multiple columns in a WHERE IN subquery causing operand errors. Avoid: Project exactly one column inside the inner SELECT statement.
Mistake: Omitting the table alias on a FROM clause subquery. Avoid: Always append an explicit alias like AS derived_table after the closing parenthesis.
Mistake: Forgetting to alias aggregated inner columns and referencing unquoted names. Avoid: Assign explicit column aliases inside the inner query for clean outer references.

FAQs

Why does MySQL throw 'Operand should contain 1 column(s)' in a WHERE subquery? The comparison operator expects a single scalar or one-dimensional list, but the inner SELECT projected two or more columns.
Why use a SELECT scalar subquery instead of a direct AVG() function? Directly calling AVG() requires a GROUP BY which collapses rows, whereas a scalar subquery preserves individual row granularity alongside the aggregate.
Why is a table alias mandatory for subqueries in the FROM clause? The SQL parser treats the subquery as a temporary derived table and requires an identifier to bind columns and resolve ambiguous references.
When should derived tables be replaced with CTEs or temporary tables? Derived tables are ideal for quick inline summaries, but Common Table Expressions (CTEs) or temp tables offer better readability and reusability for complex multi-step queries.

Common pitfalls

Mistake: Returning multiple columns in a WHERE IN subquery causing operand errors. Avoid: Project exactly one column inside the inner SELECT statement.
Mistake: Omitting the table alias on a FROM clause subquery. Avoid: Always append an explicit alias like AS derived_table after the closing parenthesis.
Mistake: Forgetting to alias aggregated inner columns and referencing unquoted names. Avoid: Assign explicit column aliases inside the inner query for clean outer references.

Hello everybody, in this lesson, we're going to be taking a look at subqueries in MySQL. Now, subquery is basically just a query within another query. We can do this in a few different ways, and I'm going to try to show you a lot of the different variations within this lesson. The first way that we're going to use a subquery is in the WHERE clause, then we'll take a look at the SELECT and the FROM clause also. Let's take this demographics table that we have down here. What if we only wanted to select the employees who worked in the actual Parks and Rec department? Well, we could do that if we had a few joins. We have the salary table, and one actually represents that they work for the Parks and Rec. If we come over here, and we open this up, we can see that Parks and Rec is the department_id of one. So, we do have that option, we could just join these two tables together, but sometimes we don't want to do that, and we'll use a subquery. Let's see how it works in the WHERE clause. So let's go ahead and get rid of this. So what we're going to do is we're going to say select everything from employee_demographics where and now we want to pull cuz this is the salary table, we want to pull employee_ids where the dept_id is equal to one. But remember, we're querying off of this table. So let's actually pull this up. This is what we're working with. So we want to say where the employee_id, that's referencing this column in the demographics table, is in what we're going to do is we're going to do a parentheses here, and we can even come down and put a parentheses down here. So what we're going to do now is write our query, which is our subquery, and this is our outer query. So now we're going to write an entirely other query within this. So we'll say select, and now we're going to say employee_id, and let's just bring this over. I usually have it something like this, and I'm going to try to bring this down a little bit. So select everything, and then we'll do from, then instead of employee_demographics, we'll do employee_salary. Now let's just format this a little better. So select the employee_id from employee_salary, and remember we wanted to do where the dept_id is equal to one. Now let's bring this back up. And this is what the query is going to look like. Now, just by itself, let's run this subquery or this inner query. When we run this, it's going to create this list of just employee_ids where the dept_id is equal to one. So, when we say where the employee_id from the employee_demographics table is in, it's going to try to match those employee_ids to this list of employee_ids. So just remember 1, 2, 3, 4, 5, 6, and 12. Let's go ahead and run this entire query. Now, we have 1, 3, 4, 5, 6, 12. If you remember from previous lessons, the two is Ron Swanson, and he's only in the salary table. So since we're doing just the employee_demographics table, he's not in here. So what we're doing is we're selecting everything from the employee_demographics where the employee_id in this table matches or is in the select employee_id from the salary table where the dept_id is equal to one. In essence, this is what a subquery is. It's a query within a query. Now what would happen if we have the employee_id, but we also wanted to say the dept_id, because we just wanted to view this? Let's go ahead and try to run this. We are going to get no output, and we're going to get an error that says, "Operand should contain 1 column(s)". The operand referring to this entire thing right here, cuz this is an operator. So this is our operand, and we're returning two columns in here, which it's saying we cannot do. We have to only have one. So now if we run this, it works perfectly well. Okay, let's bring that down. Now we can also use the subquery in a SELECT statement. So let's take a look at that next. Let's go down here, and let's say we want to do select everything from employee_salary. And let me spell that right. Let's say we want to look at all the salaries just like how we have it now, but in a column next to it, we also want to compare it to the average salary for everyone. So we'll be able to see, you know, whether somebody's salary is above average or below average. So, what we would try to do, potentially, is do something like first_name, salary, and average salary. And we try to run this. And of course we're going to get an error. It's going to basically tell us that we need to group by if we're doing this. So let's go back down, and let's actually add that GROUP BY. And we'll say group by first_name and salary. And we'll look at this output. And this is not looking good at all. It's just looking at the average salary for each unique row, which is Leslie, 75,000, so the average is 75,000. This is not what we're looking for, this is not what we want. Here's what we really do want. We want to just take the average salary of this entire column regardless of GROUP BY or anything else. So let's get rid of this and let's see how we can do that. So let's come right down here. We're going to say select select the average salary, and then we're going to say from, add our parentheses, cuz this is our subquery, from the employee_salary table. Just like that. Now if we run this, we should get the exact output we're looking for. So the average salary is 57,250, and we have our salary right here, so we can compare really quickly, just like that. Now we can also use a subquery in the FROM statement. So let's go down here, and let's say select everything from employee_demographics. Let's have it autocomplete for me. So we have the employee_demographics table. Now let's create a GROUP BY based off the gender column and add some aggregated functions, and I'm going to show you how you can use this as a subquery. So let's go up here, say gender, and then we'll, let's go ahead and add our GROUP BY. So we'll say group by gender as well. Now let's add a few things. So we'll do average, we'll do average age, and then we can do, let's just do all of them based off the age. We'll just do age, min of age, and count of age. Ooh! When I try to write fast, it doesn't always go right. So we have this. Let's run this. And this is what our output is going to look like. Now what if we wanted to get the average of the oldest age, or the average of the smallest ages, or you know, see what the average count is for males and females? Well, we can't do that given this table. But let's do something right here. Select everything, and then we're going to say from, and in our FROM statement, we're going to have a parentheses. We're going to paste our SELECT statement, and then close the parentheses. So we're going to select everything from this output that is right down here. So if we run just this, we're going to get an error, and forgot this was going to happen, but "Every derived table must have its own alias". So, you have to name a table. I forgot it does that. All we have to do to fix this is just name it. So we'll say as, and we'll say aggregated table. We'll just call it aggregated_table. So let's run this. And we get the exact same output. But here's the neat thing is we can now select, we can do gender, and these are actually the column names now. So I can do the average of this column right here. But I can't do it just like this because it's going to give us an error. And I'll show you why in just a second. Says "Unknown column 'age' in 'field list'". So what it's saying is is we're trying to perform an aggregated function on the aggregation of an age column. But we don't have an age column in our table right here. Let me run this again. We have a column named this exact thing. So what we actually need to do is do this backtick and backtick. This is the actual name of the column, it's not an aggregation anymore. The backtick on my laptop is right above the Tab on the far left-hand side, right under the Escape, that's where mine is. So these backticks, it's not a quote like this, it's a backtick. So you just need to find that on your keyboard. But now if we run this, it looks like we encountered another error. Says "in aggregated query without GROUP BY", that's right. Now we need to group by. So now we need to group by gender. Sometimes you got to figure this out on the fly. And it should work. There we go. So now we can perform aggregations on this table. Now, this doesn't actually work perfect because we're still grouping by the female/male, but let's get rid of this for a second. And we'll get rid of this GROUP BY entirely. And if we run this, we're now looking at the averages of this column right here, max age. Now when you're doing something like this, it's actually really smart to rename these. We'll say as avg_age, we'll say as max_age, and it makes it so much easier. You don't have to do these backticks anymore. and as min_age, and so on and so forth. And I would probably format this better and stuff like that. But we don't have to go through everything, right? I'm just kind of giving you an example. But then when we're using this table, these columns are actually named this, so I don't have to do these backticks anymore. I can just take this whole thing. Oops, let me get rid of that backtick. Now I can just take this column, cuz this is the column name. So let's go ahead and run this. And it's still going to work perfectly. So this one's pretty cool because you're basically creating this kind of like a temp table, you're just creating your own little output, then you can query off of it and you could do, you know, more advanced calculations this way. It's actually really useful. But there are better ways to do something like this, like a CTE, or a temp table that we'll look at in the advanced series. But this is at least how you can do it, and you can actually try it out using subqueries. So that is all we're going to look at today for subqueries. In the next lesson, we're going to take a look at window functions.

Frequently asked questions

Why does MySQL throw 'Operand should contain 1 column(s)' in a WHERE subquery?

The comparison operator expects a single scalar or one-dimensional list, but the inner SELECT projected two or more columns.

Why use a SELECT scalar subquery instead of a direct AVG() function?

Directly calling AVG() requires a GROUP BY which collapses rows, whereas a scalar subquery preserves individual row granularity alongside the aggregate.

Why is a table alias mandatory for subqueries in the FROM clause?

The SQL parser treats the subquery as a temporary derived table and requires an identifier to bind columns and resolve ambiguous references.

When should derived tables be replaced with CTEs or temporary tables?

Derived tables are ideal for quick inline summaries, but Common Table Expressions (CTEs) or temp tables offer better readability and reusability for complex multi-step queries.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.