Using subqueries in SQL

OCA Oracle Database 11g: SQL Fundamentals I Exam Guide
Chapter 8: Using Subqueries to Solve Problems

There are many situations in which it's appropriate to use a subquery. This chapter from OCA Oracle Database 11g: SQL Fundamentals I Exam Guide explains in detail how to use a subquery in SQL, including the types of problems that the subqueries can solve, such as star transformation, comparison purposes, generate a table from which to SELECT and more.

OCA Oracle Database 11g: SQL Fundamentals I Exam Guide, Chapter 8

Table of contents:

Define SQL subqueries
Using subqueries in SQL
List the types of SQL subqueries
Write single-row and multiple-row subqueries

Using subqueries in SQL

There are many situations where you will need the result of one query as the input for another.

Use of a Subquery Result Set for Comparison Purposes

Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements:

select avg(salary) from employees;
select last_name from employees where salary < result_of_previous_query ;

Alternatively, this example uses one statement with a subquery:

select last_name from employees
where salary < (select avg(salary)from employees); 

The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them:

select department_name from departments where department_id in
(select distinct(department_id) from employees); 

In the preceding example, the subquery is used as an alternative to a join. The same result could have been achieved with the following:

select department_name from departments inner join employees
on employees.department_id = departments.department_id
group by department_name; 

If the subquery is going to return more than one row, then the comparison operator must be able to accept multiple values. These operators are IN, NOT IN, ANY, and ALL. If the comparison operator is EQUAL, GREATER THAN, or LESS THAN (which each can only accept one value), the parent query will fail.

Star transformation

An extension of the use of subqueries as an alternative to a join is to enable the star transformation often needed in data warehouse applications. Consider a large table recording sales. Each sale is marked as being of a particular product to a particular buyer through a particular channel. These attributes are identified by codes, used as foreign keys to dimension tables with rows that describe each product, buyer, and channel. To identify all sales of books to buyers in Germany through Internet orders, one could run a query like this:

More on this book This chapter is excerpted from the book, OCA Oracle Database 11g: SQL Fundamentals I Exam Guide, authored by John Watson and Roopesh Ramklass, published by McGraw-Hill Osborne Media, May, 2008. ISBN 0071597867.

select … from sales s, products p, buyers b, channels c
where s.prod_code=p.prod_code
and s.buy_code=b.buy_code
and s.chan_code=c.chan_code
and p.product='Books'
and b.country='Germany'
and c.channel='Internet'; 

This query uses the WHERE clause to join the tables and then to filter the results. The following is an alternative query that will yield the same result:

select … from sales
where prod_code in (select prod_code from products where product='Books')
and buy_code in (select buy_code from buyers where country='Germany')
and chan_code in (select chan_code from channels where channel='Internet);

The rewrite of the first statement to the second is the star transformation. Apart from being an inherently more elegant structure (most SQL developers with any sense of aesthetics will agree with that), there are technical reasons why the database may be able to execute it more efficiently than the original query. Also, star queries are easier to maintain; it is very simple to add more dimensions to the query or to replace the single literals ('Books,' 'Germany,' and 'Internet') with lists of values.

Generate a Table from Which to SELECT

Subqueries can also be used in the FROM clause, where they are sometimes referred to as inline views. Consider another problem based on the HR schema: employees are assigned to a department, and departments have a location. Each location is in a country. How can you find the average salary of staff in a country, even though they work for different departments? Like this:

select avg(salary),country_id from
(select salary,department_id,location_id,country_id from
employees natural join departments natural join locations)
group by country_id; 

The subquery constructs a table with every employee's salary and the country in which his department is based. The parent query then addresses this table, averaging the SALARY and grouping by COUNTRY_ID.

Generate Values for Projection

The third place a subquery can go is in the SELECT list of a query. How can you identify the highest salary and the highest commission rate and thus what the maximum commission paid would be if the highest salaried employee also had the highest commission rate? Like this, with two subqueries:

select
(select max(salary) from employees) *
(select max(commission_pct) from employees)
/ 100
from dual;

In this usage, the SELECT list used to project columns is being populated with the results of the subqueries. A subquery used in this manner must be scalar, or the parent query will fail with an error.

Generate Rows to be Passed to a DML Statement

DML statements are covered in detail in Chapter 10. For now, consider these examples:

insert into sales_hist select * from sales where date > sysdate-1;
update employees set salary = (select avg(salary) from employees);
delete from departments
where department_id not in (select department_id from employees);

The first example uses a subquery to identify a set of rows in one table that will be inserted into another. The second example uses a subquery to calculate the average salary of all employees and passes this value (a scalar quantity) to an update statement. The third example uses a subquery to retrieve all DEPARTMENT_IDs that are in use and passes the list to a DELETE command, which will remove all departments that are not in use.

Note that it is not legal to use a subquery in the VALUES clause of an insert statement; this is fine:

insert into dates select sysdate from dual;
But this is not:
insert into dates (date_col) values (select sysdate fom dual);

More Complex Subqueries

In this exercise, you will write some more complicated subqueries. Use either SQL*Plus or SQL Developer. All the queries should be run when connected to the HR schema.

1. Log on to your database as user HR.
2. Write a query that will identify all employees who work in departments located in the United Kingdom. This will require three levels of nested subqueries:
   

   select last_name from employees where department_id in
   (select department_id from departments
   where location_id in
   (select location_id from locations where country_id =
   (select country_id from countries
   where country_name='United Kingdom')
   )
   );

3. Check that the result from step 2 is correct by running the subqueries independently. First, find the COUNTRY_ID for the United Kingdom:

   select country_id from countries where country_name='United Kingdom';
   
   The result will be UK. Then find the corresponding locations:
   select location_id from locations where country_id = 'UK';
   
   The LOCATION_IDs returned will be 2400, 2500, and 2600. Then find the DEPARTMENT_IDs of department in these locations:
   select department_id from departments where location_id in (2400,2500,2600);
   The result will be two departments, 40 and 80. Finally, find the relevant employees:
   select last_name from employees where department_id in (40,80);
   Write a query to identify all the employees who earn more than the average and who work in any of the IT departments. This will require two subqueries, not nested:
   
   select last_name from employees
   where department_id in
   (select department_id from departments where department_name like 'IT%')
   and salary > (select avg(salary) from employees);

Download the chapter "Using Subqueries to Solve Problems", which includes practice exercises and a self-test, in PDF form.

Continue to the next section: List the types of SQL subqueries

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Chapter excerpts from Oracle books Writing single-row and multiple-row subqueries List the types of SQL subqueries Define SQL subqueries Oracle 11g: PL/SQL Basics Oracle 11g: Backup and recovery concepts Migrating to Oracle: Expert Secrets to Migrate from SQL Server and MySQL Oracle Database 11g SQL Tuning Upgrading to Oracle Database 11g Tuning the Oracle database with initialization parameters Protecting your online Oracle data Oracle and SQL Can I specify Oracle column order in my database table? Review: Oracle's 11g R2 database has some good and bad SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL datatypes in Oracle PL/SQL functions and triggers in Oracle Stored procedures in PL/SQL Do I need a license for SQL Developer Data Modeler in Oracle? Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary autonomous transaction  (SearchOracle.com) CFML  (SearchOracle.com) dynamic SQL  (SearchOracle.com) foreign key  (SearchOracle.com) Java Database Connectivity  (SearchOracle.com) Open Database Connectivity  (SearchOracle.com) Oracle  (SearchOracle.com) stored procedure  (SearchOracle.com) The Open Group  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary