Guide to Oracle SQL (Buffalo State University Version)

Using Subqueries to Solve Problems

At the end of the last chapter I showed a query to display customers who have not yet placed an order. That query used a subquery. This is a very common task. Quite often you do not have the data in a table that you would need to solve a problem. The "customers without orders" is just such a problem. You need to form a subquery to find all the orders for and then pass that result set "up" to the parent query to use as a kind of "temporary table". A subquery can be used in the SELECT, FROM, WHERE, and HAVING statements.

SELECT c.customer_id AS "Customer ID", 
cust_last_name AS "Last Name", 
cust_first_name AS "First Name"  
FROM customers c
WHERE NOT EXISTS
  (SELECT * FROM orders o
   WHERE c.customer_id = o.customer_id)
ORDER BY c.cust_last_name, cust_first_name;

Customer ID Last Name            First Name     
----------- -------------------- ---------------
          8 Bogart               Sally          
          3 Buckley              Charlotte      
          1 Edwards              Sally          
         25 Gerland              Jim            
          6 Hackman              Ridley         
          2 Hitchcock            Bo             
          7 Landis               Marilou        
          9 Sen                  Dianne         
          4 Sharif               Bob            
          5 Spielberg            Sachin         
         10 Stanton              Alexander      

11 rows selected.

Figure 8-1: Result Set That Does Not Meet a Condition

Using a STAR Subquery

Suppose you wanted to identify all products that were sold through a specific channel . The next figure illustrates two (2) different approaches to solve this task. The first example uses a number of conditions on the WHERE clause. The second example uses a "star" query. The "star" query looks more elegant and actually is more efficient.

SELECT p.product_id AS "Product", p.product_desc AS "Description"
FROM sales s, channels c, products p
WHERE s.product_id = p.product_id
  AND s.product_id = p.product_id
  AND s.channel_id = c.channel_id
  AND p.product_desc LIKE '%able'
  AND c.channel_desc = 'USPS';

   Product description         
---------- --------------------
         3 Power cable  
  
SELECT product_id AS "Product", channel_id AS "Channel ID"
FROM sales
WHERE product_id IN (SELECT product_id FROM products WHERE product_desc LIKE '%able')
  AND channel_id IN (SELECT channel_id FROM channels WHERE channel_desc = 'USPS');

   Product Channel ID
---------- ----------
         3          3

Figure 8-2: An SQL Star Query

Using a Subquery To Create a Temporary Table

Suppose you wanted to identify the average salary for employees who are in different departments, locatoins, and cuintries. To solve this task you could use a subquery that uses multiple NATURAL JOIN clauses to create a result set (temporary table). which you can then query for the average salary.

SELECT TO_CHAR(AVG(salary), '$9,999,999') AS "Average Salary", country_id AS "Country ID"
FROM (
  SELECT salary, department_id, country_id, location_id 
  FROM employees
  NATURAL JOIN departments
  NATUAL JOIN locations
  )
GROUP BY country_id;

Average Sal Co
----------- --
    $55,000 CA
   $232,500 UK
    $36,400 US

Figure 8-3: Using NATURAL JOINS To Create a Temporary Table

Using `NATURAL JOINS` To Create a Temporary Table

You can also use a subquery to create a field for your SEECT statement.

COLUMN mc HEADING "Max Commission" FORMAT A14
SELECT 
  TO_CHAR(
    SELECT MAX(salary) FROM employees ) *
    (SELECT MAX(commission_pct) FROM employees ) / 100) / 100, '$9,999,999'
  )
  AS mc
FROM dual;

Max Commision 
--------------
     $4,440

Figure 8-4: Using NATURAL JOINS To Create a Temporary Table

Using Subqueries To Insert Records From One Table to Another

A common new year task is to produce the W-2 forms for that past year for all your employees and then to move all your employees who are no longer with the company into an "Inactive Employees" table. You can use a subquery on your INSERT query to insert these records and then use a DELETE query to remove those records from the "active employees" table.

SELECT last_name || ', ' || first_name AS "Employee", inactive_date AS "Last day worked" 
FROM employees 
WHERE inactive_date > '01-Jan-12';

Employee                                        Last day 
----------------------------------------------- ---------
Scott, Steve                                    01-JUL-12

INSERT INTO employees_history
  SELECT * FROM employees WHERE inactive_date > '01-Jan-12';
  
DELETE FROM employees WHERE inactive_date > '01-Jan-12';

1 row inserted.

1 row deleted.

Figure 8-5: Using a Subquery to Populate Another Table

Using Correlated Subqueries

Some tasks require that you create a subquery that uses a field from the parent query. You can use a Correlated" subquery for this. It is also known as a " repeating subquery" or a "synchronized subquery". In the next figure I use the "ep" alias to refer to the employees table in the parent query and "ec" as the employees table in the child subquery.

SELECT ep.last_name AS "Employee", ep.department_id AS "Department ID"
FROM employees ep
WHERE ep.salary 
  (SELECT AVG(ec.salary) 
   FROM employees ec
   WHERE ec.department_id = ep.department_id);

Employee                  Department ID
------------------------- -------------
Oates                                80
Hall                                 80
Taylor                               80

Figure 8-6: Using a Correlated Subquery