Sub SQL Queries
Northern Arizona University College of Business
The Sub Query Concept
s
A sub query is a query that is nested inside the where clause of another query.
The sub query is executed first and its resulting value or values is/are treated as if they were literal constants when the outer query is executed. A sub query can contain another sub query so that we can nest sub queries to any desired depth.
s
A sub query allows you to break a complex retrieval problem into parts.
"First I will retrieve a set of information X, and then I will use this result to find another set of information Y. e.g, I want to retrieve a list of Flights whose fare is less than the average fare. First I need to find the average fare, then compare each flight's fare to the average.
Sub Query
Lets assume that we wanted to look at all the flights from PHX to LAX that had fares less than the average fare for that route. The first part of the operation would be to get the average fare for that route. We could do that in one SQL query and then use the result to construct another SQL query. We can also write the query as one query where the average calculation is obtained in the sub query. Note that the sub query is in parenthesis. Select Flight_no, Orig, Dest, Fare from flight where Orig = `PHX' and Dest = `LAX' and fare < (Select avg(fare) from flight where Orig = `PHX' and Dest = `LAX')
Sub Query Example
Types of Sub Queries
s
A sub query can return a single value - single row
Most where clause operators can be used with this type of sub query In the previous example, the sub query returned a single value.
s
A sub query can return multiple rows
Can only be used with where clause operators designed to accept a list of items, such as the IN operator. (ANY and ALL operators which are described in your reading assignments also work here)
s
A sub query can return values from multiple columns
Can only be used with a special where clause option which allows combining columns and will be described below.
Sub Query Example 2
As noted on the previous slide, sub queries can be used to obtain a list of values that another query can use to control its results. For example lets assume that we are interested in obtaining the flights that originate in airports where the elevation is greater than 1000 feet. This list can be solved using a join, but it can also be solved with a sub query. First, we get a list of the codes for airports over 1000 feet in the sub query, then we apply that list in the outer query to retrieve data from all flights whose origin is in the list.
Example: Select Flight_no, Orig, Dest from flight where orig in (Select air_code from airport where elevation > 1000)
Sub Select using a List
NOTE A Sub Query as an Implicit Join:
The sub select example we just completed is a special form of sub select that is also called an implicit join. An implicit join can be used in place of a join when the data of the column list come from only one of the tables involved. The SELECT statements below produce the same result:
Select Flight_no, Orig, Dest from flight Implicit Join where orig in via Sub Select (Select air_code from airport where elevation > 1000)
Select F.Flight_no, Orig, Dest from flight F,airport A where Orig = Air_Code and Elevation >1000; Explicit Join
Multi Column Sub Select Statements
s
s
s
In some cases, you will want to use values returned from two or more columns in a sub select to identify the selection criteria to be applied in an outer select. Multiple column criteria can be identified in the where clause of a select statement by enclosing two or more column names in parentheses and separating them by commas, e.g: WHERE (Orig, Meal) IN (SELECT Orig, Meal FROM .....) The multiple columns are logical related by and logic
that is, a row meets the criteria only if all items on the left side of the equation are matched by corresponding values returned on the right side .
In our example, the Orig would need to be in the set of Orig values returned by the sub select and the Meal would need to be in the set of Meal values returned by the sub select.
Multi Column Sub Select Example
Suppose we want a list of passengers who will be on flights with the passenger named Andy Anderson. (That is, passengers flying on the same flight_no on the same flight_date). Here we first want to find the flight_no's and dates that Andy is flying (the sub query) and then get the names of all passengers on those Flight_nos on those dates (the outer query).
select pass_name, flight_no, flight_date from ticket t, passenger p where p.itinerary_no = t.itinerary_no and (flight_no, flight_date) in (select flight_no, flight_date from ticket t, passenger p where p.itinerary_no= t.itinerary_no and pass_name = 'ANDY ANDERSON')
Multi Column Sub Select Results
select pass_name, flight_no, flight_date from ticket t, passenger p 2 where p.itinerary_no = t.itinerary_no and 3 (flight_no, flight_date) in (select flight_no, flight_date 4 from ticket t, passenger p 5 where p.itinerary_no= t.itinerary_no 6* and pass_name = 'ANDY ANDERSON') SQL> / PASS_NAME FLIGHT_NO FLIGHT_DA -------------------- --------- --------ANDY ANDERSON 102 18-FEB-05 GLORIA ANDERSON 102 18-FEB-05 ANDY ANDERSON 103 19-FEB-05 ANDY ANDERSON 518 21-FEB-05 GLORIA ANDERSON 518 21-FEB-05 ANDY ANDERSON 606 20-FEB-05 GLORIA ANDERSON 606 20-FEB-05 1
Review Questions Write up your Select statement and execute it, then check your results by clicking the S button.
(All questions are based on tables in the Airwest database)
s
s
s
We want to get a listing of the name (air_location) and elevation of all airports whose elevation is greater than the average airport elevation. We want a listing of the name and elevation of all airports which serve as the origin for flights that serve breakfast (Meal = B). (We're afraid we won't be able to get those heavy airline omlettes off the ground) We need a list of all flights whose whose origin airport (orig) is the same as flight 102's destination airport (dest) and whose destination airport is the same as flight 102's origin airport (return flight for flight 102).
S
S
S
Queries Using Multiple Aliases for a Table
s
s
The query processing software of a relational DBMS is designed to take only a single pass through each table appearing in a select statement. If multiple passes are needed, we may use a sub select
the first sub select example we used was an example of this. We went through the Flight table once to determine an average fare. Then the outer select looked through the flight table again comparing individual rows to this average.
s
Alternatively, we may include the same table more than once in a select statement by using different aliases.
When multiple aliases for a table are created each is treated as a separate table when the select statement is executed and we can, in effect, search through the same table more than once.
Multiple table Alias Example The Dual Relationship Between Airports and Flights
s
s
s
s
The airline database provides a classic example of a case where multiple aliases for a table are needed. There is a dual relationship between Airport and Flight. That is, each Flight has a relationship to two different Airports, one that it comes form, and one that is going to. Note that a standard where clause to join the Airport and Flight tables would not work here. There is no single row of the Airport table which matches both origin and destination. What is needed is two copies of the airport table, one associated with the origin airport and the other associated with the destination airport. The next slide shows this for a query where we wanted the airport code and full airport name for both the origin and destination airport.
Select with Multiple Copies of the Airport Table
The select statement below creates the logical relationships shown to the right. The 2 copies of the AIRPORT table appear only in temporary storage as the select statement is executed. Physical storage of the AIRPORT table in the database is unaffected. AIRPORT OA
origin
AIRPORT DA
destination
FLIGHT
1 select flight_no, orig, oa.air_location as orig_airport_name, 2 dest, da.air_location as dest_airport_name 3 from airport oa, airport da, flight f 4 where oa.air_code = orig 5* and da.air_code = dest FLIGHT_NO --------101 102 103 104 15 17 31 . ORI --FLG PHX MPS PHX PHX PHX PHX . ORIG_AIRPORT_NAME --------------------------Flagstaff AZ Phoenix AZ Minneapolis/St Paul MN Phoenix AZ Phoenix AZ Phoenix AZ Phoenix AZ . DES --PHX MPS PHX FLG LAX LAX LAX . DEST_AIRPORT_NAME -------------------------Phoenix AZ Minneapolis/St Paul MN Phoenix AZ Flagstaff AZ Los Angeles CA Los Angeles Los CA Angeles CA .
Using Multiple Table Aliases An Intra-table Relationship Example
s
s
Multiple copies of a table are also often needed when a table contains an intra-table relationship (some rows of the table have a relationship to other rows of the same table). The example below shows an employee table with this type of relationship. The Sup_ID column is used to link an employee's row to the row of the employee's supervisor.
An employee is supervised by at most one other employee, while a supervisory employee can supervise more than one employees
E_ID ---5678 3456 4567 1234 2345 6789 7890
E_NAME --------------Gates Jones Smythe Adams Bates Lewis Earle
HIRE_DATE SALARY SUP_ID --------- --------- ---10-JAN-91 80000000 12-FEB-03 80000 5678 18-MAR-98 75000 5678 14-OCT-04 32500 3456 12-DEC-99 35000 3456 12-NOV-02 38000 4567 08-OCT-02 38000 4567
< supervises supervised by > EMPLOYEE
Intra-Table Relationship Query Example
Based on the table on the previous slide, to retrieve information about each employees supervisor, their name for example, along with information about the employee, we would need to create an alias (S) for the employee table to search for row's corresponding to the supervisor of the employee found in the original (E) copy of the table.
1 Employee S
Employee E
select e.e_id, e.e_name, s.e_id as super_id, s.e_name as super_name 2 from employee e, employee s 3* where e.sup_id = s.e_id E_NAME --------------Jones Smythe Adams Bates Lewis Earle SUPER _ID 5678 5678 3456 3456 4567 4567 SUPER_NAME --------------Gates Gates Jones Jones Smythe Smythe
E_ID ---3456 4567 1234 2345 6789 7890
Correlated Sub Query
A correlated sub query is a sub query that operates with variable values in the sub query that were obtained from the main query. Often times these variables reference the same table. For example, suppose we want to identify all flights originating from PHX whose fare is at the lowest available rate for all flights from PHX to the destination the particular flight is going to. Note how variables in the sub query refer to values obtained in the main query in this example. We want to compare each flight's fare to the minimum of fares from PHX to the destination of that particular flight. Thus the Sub query must reference the destination of the row of the flight table used in the outer query.
1 select flight_no, orig, dest, fare, meal from 2 flight f1 3 where orig = 'PHX' 4 and fare = (select min(fare) from flight f2 5 where f1.dest = f2.dest 6* and orig = 'PHX')
Correlated Sub Query Example
select flight_no, orig, dest, fare, meal from 2 flight f1 3 where orig = 'PHX' 4 and fare = (select min(fare) from flight f2 5 where f1.dest = f2.dest 6* and orig = 'PHX') SQL> / Subqueries are always executed before FLIGHT_NO --------102 104 15 31 33 35 40 600 604 606 ORI --PHX PHX PHX PHX PHX PHX PHX PHX PHX PHX DES FARE M --- --------- MPS 156 L FLG 48.5 S LAX 49 B LAX 49 S LAX 49 S LAX 49 S LAX 49 SFO 109 B SFO 109 B SFO 109 L the main query. However, the sub query in this case contains data from the main query. Thus, the sub query is executed once for each row of the main query. The values from the main query for the current row are substituted into the subquery, then it is executed and the results are used to determine whether a row is produced from the main query. This process is completed for each row of the main query. Note how time consuming this could be for a table with many rows. Correlated queries are to be avoided if there are other ways to produce the needed results. 1
In-line Views An Alternative to Correlated Sub-Queries
s
s
s
Oracle (and IBM's DB2) now support the use of in-line views An in-line view is just a sub-select that is placed in the FROM tables component of a query and given an alias Sub-select is performed first and its results treated as a temporary table when executing the outer select
In-line view example
select flight_no, orig, f.dest, fare, meal from flight f, (select dest, min(fare) as min_fare from flight where orig = 'PHX' group by dest) fm where orig = 'PHX' and f.dest = fm.dest DEST MIN_FARE LAX 49 and fare = fm.min_fare
FLIGHT_NO ORIG DEST 102 PHX MPS 104 PHX FLG 15 PHX LAX 31 PHX LAX 33 PHX LAX 35 PHX LAX 40 PHX LAX 600 PHX SFO 604 PHX SFO 606 PHX SFO FARE MEAL 156 L 48.5 S 49 B 49 S 49 S 49 S 49 109 B 109 B 109 L SFO FLG MPS 109 48.5 156
Review Questions Write up your Select statement and execute it, then check your results by clicking the S button.
(All questions are based on tables in the Airwest database)
s
s
s
We need a listing showing the flight number, fare, origin airport code and phone number and destination airport code and phone number for all flights whose fare is less than 60 dollars. (note phone numbers must come form airport and both origin and destination airports phone numbers are needed) We want a listing of all flights that can serve as a connecting flight for flight 101. That is, the origin airport (orig) must equal the destination airport (dest) of flight 102 and the orig_time must be at least 45 minutes, but no more than 300 minutes after flight 102 is scheduled to arrive (dest_time + 45/3600 adds 45 minutes to a dest_time) For each route (orig and de...