SELECT CLAUSE

Common Use Case
Often, there is a need to include data at multiple levels of detail within a single result set. For example, a task may require a value within each row of a table to be compared to the result of some aggregate (e.g., average, sum, min, etc.) function computed across those rows belonging to the same group.

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
Explore the following two real-world examples to obtain a better understanding on how this type of subquery can be used.

Above, Equal to, or Below Average Order Total?
Consider the task of computing a difference involving two values at different levels of detail, the order total for a specific order and the average order total for the corresponding customer. In the example below, there exists one row per order in the orders table, and within each row are attributes describing the order, such as the customer having placed the order and the total.

To compute the desired row-level difference, a subquery can be used. For each row in the outer query (i.e., order), the average order total is computed for the customer referenced in the row. It’s important to note the WHERE clause within the subquery ensures the orders corresponding to the customer referenced in the row are used to compute the average. This computation is made one time for each row produced by the outer query. So, if there are 105 orders, then the subquery is executed 105 times. See the query and corresponding result set below.

SELECT
	oe.orders.order_id,
    oe.orders.customer_id,
    oe.orders.order_total,
    (
        SELECT
        	AVG(average_order.order_total)
        FROM
        	oe.orders average_order
        WHERE
			oe.orders.customer_id = average_order.customer_id
    ) AS average_order_total,
    oe.orders.order_total - (
        SELECT
        	AVG(average_order.order_total)
        FROM
        	oe.orders average_order
        WHERE
    		oe.orders.customer_id = average_order.customer_id
    ) AS difference 
FROM
	oe.orders;

Above, Equal to, or Below Average Salary?
The example above likely rings a bell. It shares similar characteristics and falls into the same theme or type of problem as the one previously solved where there was a need to compute the difference between an employee’s salary and the average salary of those employees within the same job role. In that example, a subquery in the FROM clause was used. Subqueries in the SELECT clause provide another way to solve the same problem. I believe we can all agree it’s nice to have options when solving problems.

Below is another solution to that same problem using a subquery in the SELECT clause as opposed to the FROM clause. While the topic of performance is not within the scope of this page, it’s wise to compare the performance of candidate solutions and choose the most optimal one given the environment you’re operating within. Context matters!

SELECT
	hr.employees.employee_id,
    hr.employees.job_id,
    hr.employees.salary,
	(      
        SELECT
            AVG(job_salary.salary) AS average_salary
        FROM
            hr.employees job_salary
        GROUP BY
            job_salary.job_id
        HAVING
            hr.employees.job_id = job_salary.job_id
    ) AS average_salary,
    hr.employees.salary - (
        SELECT
    		AVG(job_salary.salary) AS average_salary
        FROM
            hr.employees job_salary
        GROUP BY
            job_salary.job_id
        HAVING
            hr.employees.job_id = job_salary.job_id
    ) AS difference
FROM
	hr.employees
ORDER BY
    difference DESC;

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