Scalar Subquery

Contact Us or call 1-877-932-8228
Scalar Subquery

Scalar Subquery

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.

Code Sample:

  	SELECT avg(salary) 
	FROM employees) AS avg_sal, 
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.

Nesting Subqueries

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.

Code Sample:

SELECT count(*) 
	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.