How can SELECT be used based on values from another SELECT in MySQL?

---

In MySQL, a subquery is a SELECT statement embedded within another SELECT statement.

This can be particularly useful when you want to select specific values returned from another query.

You can use the following basic syntax to use a subquery:

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

The embedded SELECT statement first selects the values from the team column in the conference table only for the rows where the conf column is equal to ‘West’.

Then, the outer SELECT statement selects the values from the id and points columns in the athletes table only for the rows where the team column is in the list of team names returned from the inner SELECT statement.

Lastly, we order the results by the values in the id column.

The following example shows how to use this syntax in practice.

Example: How to SELECT Based on Values from Another SELECT in MySQL

Suppose we have the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  id INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Magic', 14);
INSERT INTO athletes VALUES (0003, 'Lakers', 37);
INSERT INTO athletes VALUES (0004, 'Knicks', 19);
INSERT INTO athletes VALUES (0005, 'Warriors', 26);

-- view all rows in table
SELECT * FROM athletes;

Output:

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

And suppose we have another table named conference that contains information about team names and their conference:

-- create table 
CREATE TABLE conference (
  team TEXT NOT NULL,
  conf TEXT NOT NULL
);

-- insert rows into table
INSERT INTO conference VALUES ('Mavs', 'West');
INSERT INTO conference VALUES ('Magic', 'East');
INSERT INTO conference VALUES ('Lakers', 'West');
INSERT INTO conference VALUES ('Knicks', 'East');
INSERT INTO conference VALUES ('Warriors', 'West');

-- view all rows in table
SELECT * FROM conference;

Output:

+----------+------+
| team     | conf |
+----------+------+
| 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;

Output:

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

Notice that only the rows with the id values of 1, 3 and 5 are returned, which all correspond to teams that are in the West conference.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Select Last N Rows from Table