Sub-Selects & Table Joins

1. Sub-Selects & Table Joins CIS 310

2. The Sub Query Concept • 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. • 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 Products whose unit_weight is more than the average unit_weight. First I need to find the average of unit_weight, then compare each product’s unit_weight to the average.

3. Lets assume that we want to look at all the PRODUCTs whose Unit_Weight is more than the average Unit_Weight of all PRODUCTs. The first part of the operation would be to get the average Unit_Weight of PRODUCTs. 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 Prod_Code, Prod_Descrip, Unit_Weight from PRODUCT where Unit_Weight > (Select avg(unit_weight) from PRODUCT); Sub Query

4. Sub Query Example

5. Lets assume that we want to look at all the PRODUCTs whose Unit_Weight is more than the average Unit_Weight of PRODUCTs in the ‘STO’ product category. Note that parallel WHERE clause conditions are needed in the outer and sub-selects. We want to compare the weight of each PRODUCT in the STO category with the average weight of PRODUCTs in that category. Select Prod_Code, Prod_Descrip, Unit_Weight from PRODUCT WHERE Prod_Category = ‘STO’ AND Unit_Weight > (Select avg(unit_weight) from PRODUCT WHERE Prod_Category = ‘STO’); The row selection of the WHERE clause is performed first, prior to performing any aggregation or computation, thus only PRODUCTs in the STO category are included in the computed average. This also applies to queries with GROUP BY and HAVING clauses. WHERE clause selection is preformed first, then the aggregation and grouping operations are performed on the remaining rows. Sub Query – with WHERE Clause

6. Types of Sub Queries • 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. • 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 text also work here) • 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 not be described here.

7. 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 Names of all CUSTOMERs who have placed an order with Apex with a total bill greater than $5000. First, we get a list of the customer numbers associated with SALEs whose sale_tot_bill is greater than $5,000 in the sub query (on the SALE table, then we apply that list in the outer query to retrieve the names of the CUSTOMERs. EXAMPLE: select f_name, l_name from CUSTOMER where cust_no IN (select cust_no FROM SALE WHERE sale_tot_bill > 5000); RESULT: F_NAME L_NAME ---------- ---------- Jerry Jones Chuck Lewis Mary Niles Sub Query Example 2

8. Fundamentals of SQL Select Statements with Table Joins (THE Equi-Join) • Joins in a relational database are based upon linking logically related data across tables • Where the logical link is recorded by repeating the PRIMARY KEY of the Parent Table as a FOREIGN KEY in the Child Table SELECT col_1, col_2, … FROM table1, table2, … WHERE table1_key = matching_table2_key [AND … (more join conditions or selection conditions as needed)] • Attributes appearing in more than 1 table must be identified by Table.column • Abbreviated aliases are often used for table names

9. Example SQL SELECT Using an Equi-Join • For each Sale, APEX would like to see the name of the Customer we sold to, the Order number and date and the total billed. • To get this we must join the CUSTOMER and SALE tables select f_name, l_name, ord_no, ord_date, tot_bill from customer, sale where customer.cust_no = sale.cust_no;; Table names separated by commas Must set primary key of customer table = foreign key of sale

10. Use of Table Aliases • We can follow each table name with a character (or set of chars.) which serves as an alias for that table name and can be used throughout the SQL Query. • EXAMPLE: select c.cust_no, f_name, l_name, ord_no, ord_date, tot_bill from customer c, sale s where c.cust_no = s.cust_no; Since cust_no in both tables we must indicate which table to use c and s are table aliases Table aliases used here

11. Joining Three or More Tables • To join 3 or more tables, we must simply include an appropriate where clause for each relationship needed to link the set of tables involved. • The where clause always equates the primary key column of the one side table with the parallel foreign key column of the many side table of the relationship. For example to join the three tables shown below: SALE(Ord_No, Ord_Date, . . .) PRODUCT(Prod_Code, Prod_Descrip, Prod_Category. . .) ITEM_SOLD(Ord_No, Prod_Code, Qty_Ord, . . .) SELECT * FROM SALE S, ITEM_SOLD I, PRODUCT P WHERE S.Ord_No= I.Ord_No AND I.Prod_Code= P.Prod_Code;

12. Joining Three or More Tables (Continued) • We must include tables in our SELECT statement (and apply the appropriate where clauses for joining them) if: • They contain columns, that we wish to display (in our column-list) or, • 1 or more columns from the table appear in selection conditions for the select statement, • E.G. I want a list of the names of students who have a MEMBERSHIP whose join_date is after 16-JUN-98. • Or, tables needed in the SELECT statement have no direct relationship and I must include the table to build a path to relate the requested data. • E.G. I want a list of the Prod_Code, Prod_Descripand Prod_Categoryof all PRODUCTs purchased on December 14th,2012 . • In this example the data I want to retrieve is in the PRODUCT table. However, the selection criteria I am using is in the SALE table, so it must be included. Since there is no direct linkage between these two tables, I must also include the ISEM table to provide a path to properly link products and sales together. • The SELECT Statement would be: • SELECT P.Prod_Code, Prod_Descrip, Prod_CategoryFROM PRODUCT P, ITEM_SOLD I, SALE S • WHERE P.Prod_Code= I.PROD_CODE • AND I.Ord_No= S.Ord_No • AND Ord_Date= ’14-DEC-2012’;

13. Queries Using Multiple Aliases for a Table Intra-Table Relationships • The query processing software of a relational DBMS is designed to take only a single pass through each table appearing in a select statement. • Sometimes we need to compare different rows of the same table • To do this we can 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.

14. Using Multiple Table Aliases -An Intra-table Relationship Example • Multiple copies of a table are 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 Mentor_ID column is used to link an employee’s row to the row of the employee’s mentor. • An employee is mentored by at most one other employee, while an employee can be mentor to more than one employee EMP_ID EMPAME HIRE_DATE WAGE_RATE MENTOR_ID ---------------------- ---------- --------- --------- 57 Jan Jones 02-JAN-99 18.5 28 Sam Mann 14-MAY-07 14.5 57 84 Al Cowl 04-OCT-11 9.75 28 17 Ann Davis 12-JAN-08 11.25 28 43 Arne Barnie 02-JAN-99 19.75 57 23 Will Ferret 14-JUL-02 16.5 43 < mentors mentored by > EMPLOYEE Emp_ID c3 Emp_Name vc15 Hire_datedt Wage_Rate n5,2 Mentor_Idc3

15. Intra-Table Relationship Query Example Employee M Based on the table on the previous slide, to retrieve information about each employee’s mentor, their name for example, along with information about the employee, we would need to create an alias (M) for the employee table to search for row corresponding to the mentor of the employee found in the original (E) copy of the table. Employee E select e.emp_id, e.emp_name, m.emp_id as m_id, m.emp_name as super_name from employee e, employee m where e.Mentor_id = m.emp_id; EMP_ID EMP_NAME M_ID SUPER_NAME ------ --------------- ------ --------------- 17 Ann Davis 28 Sam Mann 84 Al Cowl 28 Sam Mann 23 Will Ferret 43 Arne Barnie 43 Arne Barnie 57 Jan Jones 28 Sam Mann 57 Jan Jones