Subqueries in expressions

A subquery is a SELECT statement enclosed in parentheses. The SELECT statement can contain one and only one select list item. When used as an expression, a scalar subquery is allowed to return only zero or one value;

Within the SELECT list of the top level SELECT, or in the SET clause of an UPDATE statement, you can use a scalar subquery anywhere that you can use a column name. However, the subquery cannot appear inside a conditional expression (CASE, IF, NULLIF, ARGN).

For example, the following statement returns the number of employees in each department, grouped by department name:

SELECT dept_name, COUNT(*), ‘out of’,
(SELECT COUNT(*) FROM employee)
FROM department AS D, employee AS E
WHERE D.dept_id = E.dept_id
GROUP BY dept_name;