How to SELECT Based on Values from Another SELECT in MySQL

by

In MySQL, you can use a subquery to select rows based on values returned by another SELECT statement. This technique is useful when the filtering criteria comes from a different table or query.

A subquery is a SELECT statement placed inside another SQL statement. The inner query runs first and produces a result that the outer query uses for filtering or calculation.

For example, the following query selects athletes whose teams belong to the Western conference:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
)
ORDER BY id;

The inner query:

SELECT team
FROM conference
WHERE conf = 'West'

returns the teams in the Western conference.

The outer query then uses those team names to filter the athletes table.

What Is a Subquery in MySQL?

A subquery is a SQL query embedded inside another SQL query.

A simple example is:

SELECT *
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
);

Here, the query inside the parentheses is the subquery:

SELECT team
FROM conference
WHERE conf = 'West'

The outer query is:

SELECT *
FROM athletes
WHERE team IN (...)

The subquery produces a list of teams, and the outer query returns athletes whose team appears in that list.

A common structure is:

SELECT column1, column2
FROM table1
WHERE column3 IN (
    SELECT column3
    FROM table2
    WHERE condition
);

This approach is particularly useful when you need to filter one table using information stored in another table.

Example: SELECT Based on Values from Another SELECT in MySQL

Suppose we have a table named athletes containing information about basketball players.

We can create the table with:

CREATE TABLE athletes (
    id INT PRIMARY KEY,
    team VARCHAR(50) NOT NULL,
    points INT NOT NULL
);

Next, insert some sample data:

INSERT INTO athletes VALUES
(1, 'Mavs', 22),
(2, 'Magic', 14),
(3, 'Lakers', 37),
(4, 'Knicks', 19),
(5, 'Warriors', 26);

You can view the table using:

SELECT *
FROM athletes;

The output is:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  2 | Magic    |     14 |
|  3 | Lakers   |     37 |
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
+----+----------+--------+

Now suppose we have another table called conference containing each team’s conference.

Create it with:

CREATE TABLE conference (
    team VARCHAR(50) NOT NULL,
    conf VARCHAR(20) NOT NULL
);

Insert the conference information:

INSERT INTO conference VALUES
('Mavs', 'West'),
('Magic', 'East'),
('Lakers', 'West'),
('Knicks', 'East'),
('Warriors', 'West');

View the table:

SELECT *
FROM conference;

The result is:

+----------+------+
| team     | conf |
+----------+------+
| Mavs     | West |
| Magic    | East |
| Lakers   | West |
| Knicks   | East |
| Warriors | West |
+----------+------+

Use a Subquery to Filter Based on Another Table

Suppose we want to return the id and points for athletes whose teams belong to the Western conference.

We can use:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
)
ORDER BY id;

The output is:

+----+--------+
| id | points |
+----+--------+
|  1 |     22 |
|  3 |     37 |
|  5 |     26 |
+----+--------+

The result contains only athletes from:

Mavs
Lakers
Warriors

These are the teams identified as belonging to the West conference.

How the MySQL Subquery Works

It helps to break the query into two separate parts.

The inner query is:

SELECT team
FROM conference
WHERE conf = 'West';

It returns:

Mavs
Lakers
Warriors

The outer query is effectively asking MySQL to perform:

SELECT id, points
FROM athletes
WHERE team IN ('Mavs', 'Lakers', 'Warriors')
ORDER BY id;

This returns:

+----+--------+
| id | points |
+----+--------+
|  1 |     22 |
|  3 |     37 |
|  5 |     26 |
+----+--------+

This is the key idea behind using a subquery with IN: the inner query produces the values that the outer query uses for filtering.

Use IN with a Subquery

The IN operator is one of the most common ways to use a subquery.

The syntax is:

SELECT columns
FROM table1
WHERE column IN (
    SELECT column
    FROM table2
    WHERE condition
);

For example:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
);

The IN operator checks whether each athletes.team value exists in the result returned by the subquery.

SELECT All Columns Based on a Subquery

You are not limited to selecting only id and points.

For example:

SELECT *
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
);

The result is:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  3 | Lakers   |     37 |
|  5 | Warriors |     26 |
+----+----------+--------+

This returns every column from the matching rows in athletes.

Use a Subquery with a Different Condition

You can change the condition inside the subquery.

For example, to select athletes whose teams are in the Eastern conference:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'East'
)
ORDER BY id;

The result is:

+----+--------+
| id | points |
+----+--------+
|  2 |     14 |
|  4 |     19 |
+----+--------+

The subquery returns:

Magic
Knicks

and the outer query finds athletes belonging to those teams.

Subqueries with Numeric Values

Subqueries are not limited to text values.

For example, suppose you have a departments table and want to find employees who belong to departments located in New York.

You could use:

SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE state = 'NY'
);

The inner query returns department IDs, which the outer query uses to filter employees.

Subquery with MAX()

Subqueries are also useful when you want to compare a row against an aggregate value.

For example, suppose you want to find athletes who scored more points than the average score:

SELECT id, team, points
FROM athletes
WHERE points > (
    SELECT AVG(points)
    FROM athletes
);

The inner query:

SELECT AVG(points)
FROM athletes

calculates the average points.

The outer query then returns athletes whose points value is greater than that average.

This is an example of a scalar subquery, because the inner query returns a single value.

Subquery with MAX()

To find athletes with the highest number of points:

SELECT id, team, points
FROM athletes
WHERE points = (
    SELECT MAX(points)
    FROM athletes
);

The inner query returns:

37

The outer query then finds the athlete or athletes with 37 points.

The result is:

+----+--------+--------+
| id | team   | points |
+----+--------+--------+
|  3 | Lakers |     37 |
+----+--------+--------+

Subquery with MIN()

You can use the same technique with MIN().

SELECT id, team, points
FROM athletes
WHERE points = (
    SELECT MIN(points)
    FROM athletes
);

This returns the athlete or athletes with the lowest points.

Subquery with AVG()

Another common use is finding values above or below an average.

For example:

SELECT id, team, points
FROM athletes
WHERE points > (
    SELECT AVG(points)
    FROM athletes
)
ORDER BY points DESC;

This can be useful in analytics queries where you need to identify records that perform above an overall benchmark.

Use NOT IN with a Subquery

You can also use NOT IN to exclude values returned by a subquery.

For example, to find athletes whose teams are not in the Western conference:

SELECT id, team, points
FROM athletes
WHERE team NOT IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
)
ORDER BY id;

The result is:

+----+--------+--------+
| id | team   | points |
+----+--------+--------+
|  2 | Magic  |     14 |
|  4 | Knicks |     19 |
+----+--------+--------+

This works because Magic and Knicks are the teams that do not appear in the West-conference result.

Important: NULL Values with NOT IN

Be careful when using NOT IN with subqueries that might return NULL.

For example:

WHERE team NOT IN (
    SELECT team
    FROM conference
)

If the subquery can return NULL, the behavior of NOT IN can produce unexpected results because of SQL’s three-valued logic.

When working with potentially nullable columns, NOT EXISTS is often a safer alternative.

For example:

SELECT a.id, a.team, a.points
FROM athletes AS a
WHERE NOT EXISTS (
    SELECT 1
    FROM conference AS c
    WHERE c.team = a.team
);

Use EXISTS with a Subquery

EXISTS checks whether the subquery returns at least one row.

For example:

SELECT a.id, a.team, a.points
FROM athletes AS a
WHERE EXISTS (
    SELECT 1
    FROM conference AS c
    WHERE c.team = a.team
      AND c.conf = 'West'
);

This returns athletes whose teams have a matching row in the conference table where conf is West.

The result is:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  3 | Lakers   |     37 |
|  5 | Warriors |     26 |
+----+----------+--------+

IN vs EXISTS

Both IN and EXISTS can be used for similar filtering tasks, but they express the logic differently.

Using IN:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
);

Using EXISTS:

SELECT a.id, a.points
FROM athletes AS a
WHERE EXISTS (
    SELECT 1
    FROM conference AS c
    WHERE c.team = a.team
      AND c.conf = 'West'
);

For simple lists of values, IN is often easy to read. EXISTS can be particularly useful when you are checking whether a related row exists and can be preferable for certain correlated queries.

Performance depends on the query structure, indexes, MySQL version, data distribution, and optimizer decisions, so you should use EXPLAIN when performance matters rather than assuming one form is always faster.

Subquery vs JOIN

The same business question can often be solved with either a subquery or a JOIN.

Using a subquery:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
)
ORDER BY id;

A JOIN version is:

SELECT a.id, a.points
FROM athletes AS a
JOIN conference AS c
    ON a.team = c.team
WHERE c.conf = 'West'
ORDER BY a.id;

Both queries can produce:

+----+--------+
| id | points |
+----+--------+
|  1 |     22 |
|  3 |     37 |
|  5 |     26 |
+----+--------+

A JOIN is often preferable when you need columns from both tables. A subquery can make the filtering logic more intuitive when you only need the values produced by another query.

Subqueries in the FROM Clause

A subquery can also be used as a temporary result set in the FROM clause.

For example:

SELECT *
FROM (
    SELECT team, AVG(points) AS avg_points
    FROM athletes
    GROUP BY team
) AS team_summary;

The inner query calculates the average points for each team.

The outer query then treats that result like a derived table.

The result would look like:

+---------+------------+
| team    | avg_points |
+---------+------------+
| Mavs    |    18.3333 |
| Magic   |    14.0000 |
| Lakers  |    37.0000 |
| Knicks  |    19.0000 |
| Warriors|    26.0000 |
+---------+------------+

Notice that the subquery in the FROM clause needs an alias:

AS team_summary

Subqueries in the SELECT Clause

A subquery can also appear in the SELECT list.

For example:

SELECT
    id,
    team,
    points,
    (SELECT AVG(points) FROM athletes) AS average_points
FROM athletes;

This adds the overall average to every row.

The output will contain the individual player’s points and the overall average:

+----+----------+--------+---------------+
| id | team     | points | average_points |
+----+----------+--------+---------------+
|  1 | Mavs     |     22 |          23.60 |
|  2 | Magic    |     14 |          23.60 |
|  3 | Lakers   |     37 |          23.60 |
|  4 | Knicks   |     19 |          23.60 |
|  5 | Warriors |     26 |          23.60 |
+----+----------+--------+---------------+

The exact number of decimal places depends on the data type and expression.

Correlated Subqueries

A correlated subquery is a subquery that refers to a column from the outer query.

For example:

SELECT
    a.id,
    a.team,
    a.points
FROM athletes AS a
WHERE a.points > (
    SELECT AVG(a2.points)
    FROM athletes AS a2
    WHERE a2.team = a.team
);

This compares each athlete’s score with the average score for that athlete’s own team.

Unlike a simple independent subquery, the inner query depends on the current row being processed by the outer query.

Correlated subqueries can be powerful, but they may be more expensive on large datasets. Always evaluate their execution plan when performance is important.

Practical Uses of MySQL Subqueries

Subqueries are useful across many business and analytics applications.

Find Customers with Orders

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

This returns customers who have at least one order.

Find Products Above Average Price

SELECT product_id, product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

This identifies products priced above the overall average.

Find Employees in Specific Departments

SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
);

Find Transactions for Active Customers

SELECT transaction_id, customer_id, amount
FROM transactions
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'Active'
);

This pattern is common in financial analytics, CRM systems, and SaaS applications.

Find Products in a Category

SELECT product_id, product_name
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE category_name = 'Analytics Software'
);

This allows filtering one table using criteria stored in another table.

Performance Considerations

Subqueries are powerful, but you should consider performance when working with large datasets.

Indexes on columns used for filtering and joining can make a significant difference.

For the example above, an index on conference.team can help:

CREATE INDEX idx_conference_team
ON conference(team);

An index involving the conference filter may also be useful depending on the data and query:

CREATE INDEX idx_conference_conf_team
ON conference(conf, team);

For the athletes table, an index on team may help if the table is large and team-based filtering is frequent:

CREATE INDEX idx_athletes_team
ON athletes(team);

For actual production workloads, use:

EXPLAIN
SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
);

EXPLAIN helps you understand how MySQL plans to execute the query.

Complete MySQL Example

Here is a complete example you can copy and run:

CREATE TABLE athletes (
    id INT PRIMARY KEY,
    team VARCHAR(50) NOT NULL,
    points INT NOT NULL
);

INSERT INTO athletes VALUES
(1, 'Mavs', 22),
(2, 'Magic', 14),
(3, 'Lakers', 37),
(4, 'Knicks', 19),
(5, 'Warriors', 26);

CREATE TABLE conference (
    team VARCHAR(50) NOT NULL,
    conf VARCHAR(20) NOT NULL
);

INSERT INTO conference VALUES
('Mavs', 'West'),
('Magic', 'East'),
('Lakers', 'West'),
('Knicks', 'East'),
('Warriors', 'West');

SELECT
    id,
    points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
)
ORDER BY id;

The result is:

+----+--------+
| id | points |
+----+--------+
|  1 |     22 |
|  3 |     37 |
|  5 |     26 |
+----+--------+

Frequently Asked Questions

What is a subquery in MySQL?

A subquery is a SELECT statement embedded inside another SQL statement. It allows one query to use the result of another query.

How do I SELECT based on another SELECT in MySQL?

Use a subquery with IN:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
);

Can a subquery return multiple rows?

Yes. A subquery used with IN can return multiple rows:

WHERE team IN (
    SELECT team
    FROM conference
);

However, a scalar comparison such as:

WHERE points = (
    SELECT points
    FROM athletes
);

expects the subquery to produce a single value. If it returns multiple rows, MySQL will report an error.

What is the difference between IN and EXISTS?

IN checks whether a value exists in a set returned by a subquery. EXISTS checks whether the subquery returns at least one matching row.

Can I use a subquery instead of a JOIN?

Yes. Many queries can be written using either a subquery or a JOIN. The best choice depends on readability, required output columns, data relationships, and query performance.

Can a subquery be used with aggregate functions?

Yes. For example:

SELECT *
FROM athletes
WHERE points > (
    SELECT AVG(points)
    FROM athletes
);

What is a correlated subquery?

A correlated subquery references a column from the outer query. This allows the inner query to perform calculations based on the current outer row.

Conclusion

MySQL subqueries provide a flexible way to SELECT rows based on values returned by another SELECT statement.

For example:

SELECT id, points
FROM athletes
WHERE team IN (
    SELECT team
    FROM conference
    WHERE conf = 'West'
)
ORDER BY id;

The inner query first identifies teams belonging to the Western conference:

SELECT team
FROM conference
WHERE conf = 'West';

The outer query then uses those results to filter the athletes table.

Subqueries can also be combined with AVG(), MAX(), MIN(), EXISTS, NOT EXISTS, IN, and other SQL features. They are useful for filtering related data, comparing values against aggregates, finding records that meet business conditions, and building analytics queries.

For simple filtering across related tables, IN subqueries are often easy to read. When you need columns from both tables or more complex relationships, a JOIN may be a better choice. For production systems, indexes and EXPLAIN should be used to evaluate query performance rather than relying on assumptions about which SQL structure will always be fastest.

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

13 − 3 =