MySQL: Use SELECT Based on Values from Another SELECT


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 |
+----------+------+

We can use the following syntax to select the values from the id and points columns in the athletes table only for the rows where the team column is in the West conference within the conference table:

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 Row with Max Value in Column
MySQL: How to Select Last N Rows from Table
MySQL: How to Add Column with Default Value

Leave a Reply

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