Single Field Subqueries

This page our MySQL documentation relates to sub-queries which involve a single field or return a single field in the results of the inner query.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).


           

Explanation

The most basic subquery in MySQL 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.

Examples

As an example of this type of MySQL subquery, suppose that at a fictitious college, one of the music teachers, Sonia Oram, has called 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 only for her first period Monday morning class.

The way most MySQL 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 you can enter a MySQL 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 to be able to 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 a 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 that kind of situation, see the next section.