Lesson: Using Subqueries

A subquery is a SQL SELECT statement that is contained within another SELECT statement. Subqueries can appear in various parts of a query, including the SELECT clause, the FROM clause, the WHERE clause and the HAVING clause. The innermost query is evaluated prior to queries that contain it. Performance problems can result if an "expensive" query is nested as an inner query.

Simple Subqueries

A subquery is called simple or standard to distinguish it from a correlated subquery. It does not require a join between the subquery and the query that contains it.

Nested Subquery

A simple subquery can be specified in the WHERE clause. A subquery in the WHERE clause is sometimes called a nested subquery.

The following demos shows how to display employee information for the employee with the highest salary.

Code Sample:

SELECT * 
FROM employees 
WHERE salary = 
  (
    SELECT max(salary) 
    FROM employees
  );

Inline Views

An inline view (unlike a regular view such as the emp_details_view) is not a schema object. It is simply a subquery in the FROM clause which may be referenced with an alias.

The following query shows how to display a count of all employees whose last name begins with 'A'.

Code Sample:

SELECT count(*) 
FROM 
  (
    SELECT *
    FROM employees
    WHERE last_name LIKE 'A%'
  );

Subquery in the HAVING clause

A simple subquery can be specified in the HAVING clause. It is similar in many ways to a subquery in the WHERE clause. The HAVING clause is distinguished from the WHERE clause based upon when it is evaluated and what it evaluates. The WHERE clause is evaluated before the GROUP BY clause, while the HAVING clause is evaluated after it. Filtering predicates in the WHERE clause eliminate rows from the result set when single row (scalar) expressions are evaluated. Filtering predicates in the HAVING clause eliminate rows from the result set when aggregate functions are evaluated.

The following query uses the emp_details_view to display each department name and its average salary for those departments whose average salary is greater than the average salary for all employees.

Code Sample:

SELECT department_name, avg(salary) 
FROM emp_details_view 
GROUP BY department_name 
HAVING avg(salary) > (  
	SELECT avg(salary) 
	FROM employees
);

Subquery in the HAVING clause

A simple subquery can be specified in the HAVING clause. It is similar in many ways to a subquery in the WHERE clause. The HAVING clause is distinguished from the WHERE clause based upon when it is evaluated and what it evaluates. The WHERE clause is evaluated before the GROUP BY clause, while the HAVING clause is evaluated after it. Filtering predicates in the WHERE clause eliminate rows from the result set when single row (scalar) expressions are evaluated. Filtering predicates in the HAVING clause eliminate rows from the result set when aggregate functions are evaluated.

Using the EMP_DETAILS_VIEW display each department name and its average salary for those departments whose average salary is greater than the average salary for all employees.

Code Sample:

            

			            
This query returns the departments and the average
		         salary for each department where the average salary
		         for the department is greater than the average salary
		         for all employees.
			    
			         
            

Correlated Subqueries

A correlated subquery has a join between the subquery and the query that contains it. Table aliases are often required to clarify the relationship between tables in the inner and outer query - especially if they involve the same table. In terms of scope, the inner query can "see" an outer table's columns, but the inner query's columns are not visible to the outer query.

As the following query shows, a correlated subquery is required to discover the highest paid employee in each department.

Code Sample:

SELECT * 
FROM employees e1 
WHERE e1.salary = (
	SELECT max(salary) 
	FROM employees e2 
	WHERE e1.department_id = e2.department_id
);

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.

The following query displays the average salary of all employees alongside the salary for each employee.

Code Sample:

SELECT (
  	SELECT avg(salary) 
	FROM employees) AS avg_sal, 
	salary 
FROM employees;

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.

The following query displays a count of employees whose salary is greater than the average salary of all employees.

Code Sample:

SELECT count(*) 
FROM 
(
	SELECT salary, 
		(
			SELECT avg(salary) 
			FROM employees) avg_sal 
	FROM employees
)
WHERE salary > avg_sal;

Subqueries

1. Create a query that returns the lowest paid employee. (This will involve a subquery in the WHERE clause).
2. Create a query that returns the departments where the average salary is less than the average for all employees. (This will involve a subquery in the HAVING clause).
3. Create a query that returns the lowest paid employee for each department. (This will involve a correlated subquery).

Solution:

--
-- Lowest paid employee.
--
SELECT *
FROM employees
WHERE salary = (SELECT min(salary)
	FROM employees
);

--
-- Departments where the average 
-- salary is less than the average for all employees.
--
SELECT department_name, avg(salary)
FROM emp_details_view
GROUP BY department_name
HAVING avg(salary) < (SELECT avg(salary) 
	FROM employees
);

--
-- Lowest paid employee for each department.
--
SELECT *
FROM employees e1
WHERE e1.salary = (SELECT min(salary)
	FROM employees e2
	WHERE e1.department_id = e2.department_id
);