Error message

Notice: Undefined offset: 0 in __lambda_func() (line 4 of /var/www/mysqlresources-drupal/modules/views_php/plugins/views/views_php_handler_field.inc(202) : runtime-created function).

Results Set Subqueries

This page our MySQL documentation relates to MySQL Subqueries, in particular, subqueries which return a results set from 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

A subquery in MySQL can be used to generate a results set, which is a table from which an outer query can select data. That is to say, a subquery can be used in a FROM clause as if it were another table in a database. It is said to be a derived table. Along those 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.

Examples

In the example that follows, 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 resort 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 and 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 MySQL 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.