WHERE CLAUSE

Common Use Case
Often, there is a need to filter the results of a query based on the results of another query, dynamically. For example, consider the task of producing a report on a daily basis containing the most recent day’s business transactions. A user could update the query on a daily basis to contain the hardcoded value associated with the most recent date value. However, the more reliable and convenient option would be to use a subquery to return this date value at runtime.

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Real-world Examples
In practice, subqueries in the WHERE clause are typically used to implement dynamic filter criteria. Explore the following two real-world examples to obtain a better understanding on how this type of subquery can be used.

Most Recent Employee(s) Hired
Imagine you work within a human resource department and a recurring task is to generate a report to review the information related to the most recent employee(s) hired. Instead of retrieving the entire employee listing and associated details, sorting by hire date in descending order, and then excluding all employees with a hire date less than the most recent hire date value, a subquery in the WHERE clause can be used to first determine the hire date associated with the most recent employee(s) hired and subsequently compare the value to each employee’s hire date. It’s important to note this date value is computed at query runtime and reflects the most recent hire date based on the data available at that point in time. By using the subquery, many steps and the hardcoding of date values can be avoided. See the query and corresponding result set below.

SELECT
	hr.employees.employee_id,
    hr.employees.first_name,
    hr.employees.last_name,
    hr.employees.job_id,
    hr.employees.hire_date,
    hr.employees.salary
FROM
	hr.employees
WHERE
	hr.employees.hire_date = (
        SELECT
        	MAX(hr.employees.hire_date)
        FROM
        	hr.employees
    );

Maxed Out Salary
For managers of employees, it’s important to understand where each employee sits in their current job ID’s salary range. Does the employee have room to grow or has the employee reached the maximum salary possible for the specific job ID? Imagine you’re a manager and your task is to answer this question for all employees with the ultimate goal being to generate a report containing the employee ID, job ID, and salary for each employee that has a salary equal to the maximum salary for the job ID. There are many ways to solve this problem, including the arguably more straightforward method utilizing an INNER JOIN; however, I’ve used a correlated subquery in the WHERE clause below to maintain focus on the current topic of subqueries. See the query and corresponding result set below. A brief explanation is provided following the result set.

SELECT
	hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.salary
FROM
    hr.employees
WHERE
    hr.employees.salary - (
    	SELECT
    		hr.jobs.max_salary
    	FROM
    		hr.jobs
    	WHERE
    		hr.employees.job_id = hr.jobs.job_id
    ) = 0

In the query above, the difference between each employee’s salary and the maximum salary for their corresponding job ID is computed and compared to the value of 0 (i.e., no difference). Computing the difference is made possible by the subquery in the WHERE clause. This subquery executes one time per row in the outer query (i.e., employee) and returns the maximum salary for the specific employee’s job ID. It’s important to note the WHERE clause within the subquery ensures the correct maximum salary value from the jobs table is used to compute each difference.

Knowledge Check
If you’d like to confirm your understanding on the use of subqueries in the WHERE clause and other fundamental concepts, consider solving a handful of my practice problems here.