Subquery Nested by HAVING

Function

This statement is used to embed a subquery in the HAVING clause. The subquery result is used as a part of the HAVING clause.

Syntax

1
2
3

SELECT [ALL | DISTINCT] attr_expr_list FROM table_reference
  GROUP BY groupby_expression
  HAVING aggregate_func(col_name) operator (sub_query);

Keyword

• All is used to return repeated rows. By default, all repeated rows are returned. It is followed by asterisks (*) only. Otherwise, an error will occur.
• DISTINCT is used to remove the repeated line from the result.

• The groupby_expression can contain a single field or multiple fields, and also can call aggregate functions or string functions.
• The operator includes the equation and inequation operators, and IN and NOT IN operators.

Precautions

• The to-be-queried table must exist. If this statement is used to query a table that does not exist, an error is reported.
• The sequence of sub_query and the aggregate function cannot be changed.

Example

To group the student_info table according to the name field, count the records of each group, and return the number of records in which the name fields in the student_info table equal to the name fields in the course_info table if the two tables have the same number of records, run the following statement:

1
2
3

SELECT name FROM student_info
  GROUP BY name
  HAVING count(name) = (SELECT count(*) FROM course_info);