230 likes | 345 Views
Advanced SQL. Chapter 8 (Part A) Multiple Table Queries. Objectives. Be able to retrieve data from multiple tables Be able to use an alias Be able to join a table to itself. Retrieving Data from Multiple Tables.
E N D
Advanced SQL Chapter 8 (Part A) Multiple Table Queries
Objectives • Be able to retrieve data from multiple tables • Be able to use an alias • Be able to join a table to itself
Retrieving Data from Multiple Tables • Tables may be joined when a column from one table is the same length and type as a column in another table. • Typically, the primary key in one table is matched to the foreign key in another table. • The WHERE clause is used to join the two columns together. • Often the column names must be qualified: TableName.ColumnName
Querying Multiple Tables • Join • Indicate in the SELECT clause all the columns to display. • Indicate in the FROM clause all tables involved in the query. • Give condition(s) in the WHERE clause to restrict the data to be retrieved to only those rows that have common values in matching columns.
Join • Join creates a new table (virtual table) from two existing tables by matching on a column common to both tables • Equi-join - The new table contains the two identical columns • Natural join –Duplicate columns are eliminated. • Most commonly used join • Outer join – rows that do not have matching values in common are nevertheless included in the result table • Returns all the values in one of the tables included in the join, regardless of whether a match exists in the other table or not • Left outer join, right-outer join, full-join
Join: Equijoin • The new table contains the two identical columns Join the Student and Major tables SELECT * FROMStudent, Major WHEREStudent.Major = Major.Code ORDER BY Student.ID;
Join: Natural Join • Duplicate columns are not included Join the Student and Major tables SELECTID, Name, Major, Description FROMStudent, Major WHEREStudent.Major = Major.Code ORDER BY Student.ID; • Specify the columns to include. • If the same name in both columns, specify table: Student.ID, Student.Name etc.
Join: Natural Join • The most common join Display the number and names of students taking English 101 SELECTID, Name FROMStudent, English101 WHEREStudent.ID = English101.Student_ID ORDER BY Student.ID; Either English101.Student_ID or Student.ID could have been projected.
Join: Natural Join Display the number, name and major description of students taking English 101 SELECTID, Name, Description FROMStudent, English101, Major WHEREStudent.ID = English101.Student_ID ANDStudent.Major = Major.Code ORDER BYStudent.ID;
Join: Natural Join Display the ID, name, major and exam average for all students in English101 SELECTID, Name, Major, ((Exam1+Exam2+Exam3)/3) AS TestAvg FROMStudent, English101 WHEREStudent.ID = English101.Student_ID ORDER BYStudent.ID; Named Calculated Column, TestAvg
Join: Natural Join Group all majors together and calculate the test average per major in English101 SELECTMajor, AVG ((Exam1+Exam2+Exam3)/3) AS TestAvg FROMStudent, English101 WHEREStudent.ID = English101.Student_ID GROUP BY Major ORDER BYMajor;
Join: Outer Join • Rows that do not have matching values in common are nevertheless included in the result table SELECTEnglish101.Student_ID, Student.Name FROMStudent, English101 WHERE Student.ID = English101.Student_ID (+) ORDER BYEnglish101.Student_ID; plus (+) indicates outer join
Join: Outer Join • Display all students who are NOT enrolled in English101 SELECTStudent.Name FROMStudent, English101 WHERE Student.ID = English101.Student_ID (+) AND English101.Student_ID IS NULL ORDER BYName;
Join: Outer Join • LEFT JOIN – Include all values of left table in query • RIGHTJOIN – Include all values of right table in query • FULL JOIN – Include all rows (rarely used) SELECTEnglish101.Student_ID, Student.Name FROMStudentLEFT JOIN English101 ONStudent.ID = English101.Student_ID ORDER BYEnglish101.Student_ID; Alternative SQL notation
An Alias • Tables listed in the FROM clause can be given an alternative name • An ALIAS is an alternative name for a table that may be used when qualifying column names in order to reduce your typing. SELECTEng.Student_ID, Stud.Name FROM English101 Eng, Student Stud WHEREEng.Student_ID = Stud.ID;
Joining A Table To Itself • Find every student who has the same name as another student. SELECT Stud1.ID, Stud1.Name, Stud1.Major FROM Student Stud1, Student Stud2 WHERE Stud1.Name = Stud2.Name AND Stud1.ID <> Stud2.ID ORDER BY Stud1.ID;
Join vs Subquery • Joining tables on matching columns produces the same results as using a subquery with IN. SELECT partdesc FROM Part, OrderLine WHERE Part.partno = OrderLine.partno AND ordno = ‘12491’; SELECT partdesc FROM Part WHERE partno IN (SELECT partno FROM OrderLine WHERE ordno = ‘12491’);
Subquery within a Subquery • A nested query may be used within a nested query or multiple tables may be joined. Find the order number & date for every order that includes a part located in warehouse 3. SELECT ordno, orddate FROM orders WHERE ordno IN (SELECT ordno FROM orderline WHERE partno IN (SELECT partno FROM part WHERE warehouseno = ‘3’) );
Nested Subquery Evaluation Step 1: Innermost subquery is evaluated first, producing a temporary table of part numbers located in warehouse 3 Step 2: Intermediate subquery is evaluated, producing a second temporary table with a list of order numbers Step 3: Outer query is evaluated last, producing the desired list of order numbers and order dates using only those orders whose numbers are in the temporary table produced in step 2
ALL & ANY Operators • ALL and ANY may be used in front of a subquery. • If the subquery is preceded by ALL, the condition is true only if it satisfies ALL values produced by the subquery. • If the subquery is preceded by ANY, the condition is true if it satisfies ANY value produced by the subquery
ANY Example • Find the customer# of every customer whose balance is larger than the balance of at least one customer represented by salesman# 10. SELECT custno FROM Customer WHERE balance > ANY (SELECT balance FROM Customer WHERE salesrepno = ‘10’);
Correlated Subquery • A correlated subquery is when the inner query refers to a table used in the outer query (as the previous example did). • The inner query is computed for each outer row List the description of the product with the highest unit price. SELECT Description, Price FROM Product PA WHERE Price > ALL (SELECT Price FROM Product PB WHERE PA.id <> PB.id);