MySQueaL Resources

Multiple Fields Subqueries Statement

This page our MySQL documentation relates to MySQL Subqueries, in particular, subqueries which involve multiple fields or return multiple fields in the results.

This page our MySQL documentation relates to MySQL Subqueries. On the page of our MySQL documentation related to Single Field Subqueries in MySQL, instances were discussed in which 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 clause: ALL, ANY, EXISTS, IN, and SOME.

As an example of a multiple fields subquery in general, 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 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 querry. The criteria of the WHERE clause here does not specify a specific time slot as the example earlier 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, though, 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 that a few students are enrolled in all of the seminars that he has scheduled for the semester and that he wants a list of their names and telephone numbers in advance. We should be able to get that data by entering a MySQL statement like this (but 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(*) 
     A     A     A s_registered,
   CONCAT(name_first, ' ', name_last   CONCAT( 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

The first ster. This single value is used with the WHERE clause of 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.