A subquery that returns only a single row is called a scalar subquery. If it returns no rows, the value is NULL. It can be used (with some exceptions) in places where an expression is normally used.
Display the average salary of all employees alongside the salary for every employee.
SELECT ( SELECT avg(salary) FROM employees) AS avg_sal, salary FROM employees;
In this example, a scalar subquery in the SELECT clause is used to obtain the average salary for all rows in the table and display it beside the salary of the current row.
A subquery can be nested within another subquery. There is no limit on the number of subquery levels in the FROM clause and a limit of 255 levels in a WHERE clause. The following example is a query that has a subquery in the SELECT clause of a query that appears in the FROM clause of another query.
Display a count of employees whose salary is greater than the average salary of all employees.
SELECT count(*) FROM ( SELECT salary, ( SELECT avg(salary) FROM employees) avg_sal FROM employees ) WHERE salary > avg_sal;
When trying to decipher subqueries at multiple levels, it is helpful to think through the processing starting with the innermost query and work to the outermost query.
The innermost query is getting the average salary of all records in the employee table. This is retrieved along side the salary for each employee record in the employee table. This query is wrapped and treated as an inline view. The final query returns a count of all employees how have a salary greater than the average salary.