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
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.
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.
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.
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.
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'.
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.
Note that this example is a somewhat contrived. Usually, the inline views are more complex, querying data from multiple tables using joins.
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.
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.
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.
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.
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.
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
.
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.
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.
The following query displays 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 employees
table. This is retrieved along side
the salary for each employee record in the employees
table.
This query is wrapped and treated as an inline view. The final
query returns a count of all employees who have a salary greater than
the average salary.
WHERE
clause).HAVING
clause).-- -- 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 );