facebook google plus twitter
Webucator's Free Advanced Oracle SQL Queries Tutorial

Lesson: Using Subqueries

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based on Webucator's Advanced Oracle SQL Queries course.

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.

Lesson Goals

  • Learn about simple subqueries.
  • Learn about nested subqueries.
  • Learn about in-line views.
  • Learn about correlated subqueries.
  • Learn about scalar 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

Definition

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.

Display employee information for the employees with the highest salary.

Code Sample:

Using-Subqueries/Demos/nested_subqueries.sql
SELECT * 
FROM employees 
WHERE salary = 
(
	SELECT max(salary) 
	FROM employees
);

This query returns rows from the employees table for the employee (or employees) that has the highest salary. Because the subquery will always return exactly one row, the equality operator (or one of its siblings) should be used. If there is no guarantee that the subquery will always return exactly one row, then the IN operator should be used. Whenever possible use the equality operator because it is faster than the IN operator.

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.

Display a count of all employees whose last name begins with 'A'.

Code Sample:

Using-Subqueries/Demos/inline_view.sql
SELECT count(*) 
FROM 
(
	SELECT *
	FROM employees
	WHERE last_name LIKE 'A%'
);

This query uses an inline view. The result is a count of the total number of records that are returned by the nested query. (In fact, any query could be replaced as the inner query and a count of the rows will be returned). In the example of nested subqueries, the outer query used here is used again with a different inner query.

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:

Using-Subqueries/Demos/simple_subquery_having.sql
SELECT department_name, avg(salary) 
FROM emp_details_view 
GROUP BY department_name 
HAVING avg(salary) > (  
	SELECT avg(salary) 
	FROM employees
);

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.

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:

Using-Subqueries/Demos/simple_subquery_having.sql
            

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 a 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.

To discover the highest paid employee in each department, a correlated subquery is required.

Code Sample:

Using-Subqueries/Demos/correlated_subquery.sql
SELECT * 
FROM employees e1 
WHERE e1.salary = (
	SELECT max(salary) 
	FROM employees e2 
	WHERE e1.department_id = e2.department_id
);

Both the inner and outer query reference the employees table. The outer query's employees table is aliased as e1, and the inner query's employees table is aliased as e2. Within the inner query there is a join on 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.

Display the average salary of all employees alongside the salary for every employee.

Code Sample:

Using-Subqueries/Demos/scalar_subquery.sql
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.

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:

Using-Subqueries/Demos/nesting_subqueries.sql
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.

Subqueries

Duration: 10 to 15 minutes.
  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:

Using-Subqueries/Solutions/subquery_solutions.sql
--
-- 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
);