Table of Contents for

MySQL in a Nutshell, 2nd Edition

The most basic subquery is one that returns a scalar or single value. This type of subquery is particularly useful in a WHERE clause in conjunction with an = operator, or in other instances where a single value from an expression is permitted.

As an example of this situation, suppose that at our fictitious college one of the music teachers, Sonia Oram, has called us saying that she wants a list of students for one of her classes so that she can call them to invite them to a concert. She wants the names and telephone numbers for only the students in her first period Monday morning class.

The way most databases store this data, the course number would be a unique key and would make it easy to retrieve the other data without a subquery. But Sonia doesn’t know the course number, so we enter an SQL statement like this:

SELECT CONCAT(name_first, ' ', name_last) AS student,
phone_home, phone_dorm
FROM students
JOIN course_rosters USING (student_id)
WHERE course_id =
  (SELECT course_id 
   FROM course_schedule
   JOIN teachers USING (teacher_id)
   WHERE semester_code = '2007AU'
   AND class_time = 'monday_01'
   AND name_first = 'Sonia'
   AND name_last = 'Oram');

Notice in the subquery that we’re joining the course_schedule table with teachers so we can give the teacher’s first and last name in the WHERE clause of the subquery. We’re also indicating in the WHERE clause a specific semester (Autumn 2007) and time slot (Monday, first period). The results of these specifics should be one course identification number because a teacher won’t teach more than one class during a particular class period. That single course number will be used by the WHERE clause of the main query to return the list of students on the class roster for the course, along with their telephone numbers.

If by chance more than one value is returned by the subquery in the previous example, MySQL will return an error:

ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = "Subquery returns more than 1 row"

Despite our supposition, it is possible that a teacher might teach more than one class at a time: perhaps the teacher is teaching one course in violin and another in viola, but each class had so few students that the department head put them together. In such a situation, the teacher would want the data for both course numbers. To use multiple fields derived from a subquery in a WHERE clause like this, we would have to use something other than the = operator, such as IN. For this kind of situation, see the next section on Multiple Fields Subqueries.”

In the previous section, we discussed instances where one scalar value was obtained from a subquery in a WHERE clause. However, there are times when you may want to match multiple values. For those situations you will need to use the subquery in conjunction with an operator or a clause: ALL, ANY, EXISTS, IN, or SOME.

As an example of a multiple fields subquery—and specifically of a subquery using IN (or using ANY or SOME)—let’s adapt the example from the previous section to a situation where the teacher wants the contact information for students in all of her classes. To do this, we can enter the following SQL statement:

SELECT CONCAT(name_first, ' ', name_last) AS student,
phone_home, phone_dorm
FROM students
JOIN course_rosters USING (student_id)
WHERE course_id IN
  (SELECT course_id 
   FROM course_schedule
   JOIN teachers USING (teacher_id)
   WHERE semester_code = '2007AU'
   AND name_first = 'Sonia'
   AND name_last = 'Oram');

In this example, notice that the subquery is contained within the parentheses of the IN clause. Subqueries are executed first, so the results will be available before the WHERE clause is executed. Although a comma-separated list isn’t returned, MySQL still accepts the results so that they may be used by the outer query. The criteria of the WHERE clause here does not specify a specific time slot as the earlier example did, so multiple values are much more likely to be returned.

Instead of IN, you can use ANY or SOME to obtain the same results by the same methods. (ANY and SOME are synonymous.) These two keywords must be preceded by a comparison operator (e.g., =, <, >). For example, we could replace the IN in the SQL previous statement with = ANY or with = SOME and the same results will be returned. IN can be preceded with NOT for negative comparisons: NOT IN(...). This is the same as != ANY (...) and != SOME (...).

Let’s look at another subquery returning multiple values but using the ALL operator. The ALL operator must be preceded by a comparison operator (e.g., =, <, >). As an example of this usage, suppose one of the piano teachers provides weekend seminars for students. Suppose also that he heard a few students are enrolled in all of the seminars he has scheduled for the semester and he wants a list of their names and telephone numbers in advance. We should be able to get that data by entering an SQL statement like the following (though currently it doesn’t work, for reasons to be explained shortly):

SELECT DISTINCT student_id, 
CONCAT(name_first, ' ', name_last) AS student 
FROM students
JOIN seminar_rosters USING (student_id)
WHERE seminar_id = ALL
  (SELECT seminar_id 
   FROM seminar_schedule
   JOIN teachers ON (instructor_id = teacher_id)
   WHERE semester_code = '2007AU'
   AND name_first = 'Sam'
   AND name_last = 'Oram');

In this example, a couple of the tables have different column names for the ID we want, and we have to join one of them with ON instead of USING, but that has nothing to do with the subquery. What’s significant is that this subquery returns a list of seminar identification numbers and is used in the WHERE clause of the main query with = ALL. Unfortunately, although this statement is constructed correctly, it doesn’t work with MySQL at the time of this writing and just returns an empty set. However, it should work in future releases of MySQL, so I’ve included it for future reference. For now, we would have to reorganize the SQL statement like so:

SELECT student_id, student
FROM
  (SELECT student_id, COUNT(*) 
     AS nbr_seminars_registered,
   CONCAT(name_first, ' ', name_last) 
     AS student 
   FROM students
   JOIN seminar_rosters USING (student_id)
   WHERE seminar_id IN
     (SELECT seminar_id 
      FROM seminar_schedule
      JOIN teachers 
      ON (instructor_id = teacher_id)
      WHERE semester_code = '2007AU'
      AND name_first = 'Sam'
      AND name_last = 'Oram')
   GROUP BY student_id) AS students_registered
WHERE nbr_seminars_registered =
   (SELECT COUNT(*) AS nbr_seminars
   FROM seminar_schedule
   JOIN teachers 
   ON (instructor_id = teacher_id)
   WHERE semester_code = '2007AU'
   AND name_first = 'Sam'
   AND name_last = 'Oram');

This is much more involved, but it does work with the latest release of MySQL.

The first subquery is used to get the student’s name. This subquery’s WHERE clause uses another subquery to retrieve the list of seminars taught by the professor for the semester, to determine the results set from which the main query will draw its ultimate data. The third subquery counts the number of seminars that the same professor is teaching for the semester. This single value is used with the WHERE clause of the main query. In essence, we’re determining the number of seminars the professor is teaching and which students are registered for all of them.

The last possible method for using multiple fields in a subquery uses EXISTS. With EXISTS, in order for it to return meaningful or desired results, you need to stipulate in the WHERE clauses of the subquery a point in which it is joined to the outer query. Using the example from the previous section involving the teacher Sonia Oram, let’s suppose that we want to retrieve a list of courses that she teaches:

SELECT DISTINCT course_id, course_name 
FROM courses
WHERE EXISTS
  (SELECT course_id 
   FROM course_schedule
   JOIN teachers USING (teacher_id)
   WHERE semester_code = '2007AU'
   AND name_first = 'Sonia'
   AND name_last = 'Oram'
   AND courses.course_id = course_schedule.course_id);

As you can see here, we’ve added EXISTS to the WHERE clause with the subquery in parentheses, similar to using IN. The significant difference is that we added courses.course_id = course_schedule.course_id to the end. Without it, a list of all courses would be returned regardless of the criteria of the WHERE clause in the subquery. Incidentally, if we specified NOT EXISTS instead, we would get all courses except for the ones taught by the teacher given.

A subquery can be used to generate a results set, which is a table from which an outer query can select data. That is, a subquery can be used in a FROM clause as if it were another table in a database. It is a derived table. Along these lines, each derived table must be named. This is done with AS following the parentheses containing the subquery. A subquery contained in a FROM clause generally cannot be a correlated subquery—that is, it cannot reference the same table as the outer query. The exception is if it’s constructed with a JOIN.

In the following example, let’s consider the subquery separately as though it were a plain query and not a subquery. It will generate a results set containing the student’s ID and the student’s average exam score for a specific course taught during a specific semester. The query uses AVG(), which requires a GROUP BY clause. The problem with GROUP BY is that it will order data only by the columns by which it’s given to group data. In this case, it will order the data by student_id and not list the results by any other, more useful column. If we want to order the data so that the highest student average is first, descending in order to the lowest student average, we have to turn our query into a subquery and have the outer query re-sort the results:

SELECT CONCAT(name_first, ' ', name_last) AS student,
student_id, avg_grade
FROM students
JOIN
  (SELECT student_id,
   AVG(exam_grade) AS avg_grade
   FROM exams
   WHERE semester_code = '2007AU'
   AND course_id = 1489
   GROUP BY student_id) AS grade_averages
USING(student_id)
ORDER BY avg_grade DESC;

The results set (the derived table generated by the subquery in the FROM clause) is named grade_averages. Notice that although the column student_id exists in the derived table, in the table from which it gets its data (i.e., exams) and in the primary table used in the main query (i.e., students), there is no ambiguity. No error is generated. However, if we wanted to specify that the data be taken from the derived table, we could put grade_averages.student_id in the SELECT of the outer query.

This subquery is a correlated subquery, which is generally not permitted in a FROM clause. It’s allowed in this example because we are using a JOIN to join the results set to the table referenced in the outer query.