Querying Multiple Tables Pertemuan 4

1. Querying Multiple TablesPertemuan 4 Matakuliah : T0413/Current Popular IT II Tahun : 2007

2. AGENDA:•Joining Tables•Joining a Table to Itself•More Complex Uses of Joins Book: Mastering SQL by Martin Gruber Sybex (2000) Chapter : 10

3. Joining Tables • Ability to define relationships between multiple tables and draw information from the tables in terms of these relationships, all within a single statement  using JOIN. • Table and Column Names: • Consists of table name followed by a dot and then the column name. • Example: Salespeople.snum Customers.city Orders.odate

4. Joining Tables (cont’d) • Making a Natural Join • They express relationships that already exist in the structure of the database. • SELECT Customers.cname, Salespeople.sname FROM Customers, Salespeople WHERE Salespeople.snum = Customers.snum; • Making an Unnatural Join • Finding relationships based on the data content rather than the database design. • SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Customers, Salespeople WHERE Salespeople.city = Customers.city;

5. Joining Tables (cont’d) • Compound and Join Predicates • SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Customers, Salespeople WHERE Salespeople.city IN (‘London’, ‘Barcelona’) AND Salespeople.city = Customers.city; • Compound predicate • Join Predicate Salespeople.city = Customers.city • Conventional Predicate  Salespeople.city IN (‘London’, ‘Barcelona’)

6. Joining Tables (cont’d) • Equijoins and Non – equijoins • Joins that uses predicates based on equalities are called equijoins. • Most of the example in this discussion are all based on equality expressions • Because the conditions in the WHERE clause, for example: • City = ‘London’ • Salespeople.sum = Orders.snum • Equijoins are the most common sort of join.

7. Joining Tables (cont’d) • OUTER Joins, to perform it, there are 3 approaches: • Using subquery with EXIST operator • Combine two queries using UNION operator • Using special operator designed for use with OUTER joins. • Joins of More Than Two Tables • SELECT sname, cname, onum FROM Salespeople, Customers, Orders WHERE Orders, snum = Salespeople.snum AND Orders.cnum = Customers.cnum; • SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Orders.snum = Salespeople.snum AND Orders.cnum = Customers.cnum AND Customers.city <> Salespeople.city;

8. Joining a Table to Itself • Joining table to itself, it’s similar to joining it to another table, but the tables are identical. The table are not being duplicated/copied. • Aliases • To differentiate between copies of a single table • To assign alternate names for tables and/or columns in a statement • Example: SELECT first.cname, second.cname, first.rating FROM Customers first, Customers second WHERE first.rating = second.rating AND first.cname < second.cname;

9. More Complex Uses of Joins • Finding patterns in the data • Joining tables that are not used in the output • Special join operators • CROSS JOINs • Equivalent to a join without a join predicate, straight Cartesian product. • SELECT Salespeople.snum, Customers.city FROM Salespeople CROSS JOIN Customers; • NATURAL JOINs • Joins based on matching foreign key values to parent key values. • SELECT a.snum, a.sname, b.cnum, b.amt FROM Salespeople a NATURAL JOIN Orders b; Equal to : SELECT a.snum, a.sname, b.cnum, b.amt FROM Salespeople a, Orders b WHERE a.snum = b.snum;

10. More Complex Uses of Joins (cont’d) • Specified Joins (ON and USING) • Specified joins are joins that directly specify by creating a predicate that states how the join is to be done (the ON form) or by listing a set of columns that are to be equijoined (the USING form). • SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers ON Salespeople.city = Customers.city WHERE Salespeople.city IN (‘London’, ‘Barcelona’); • SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers USING (city) WHERE Salespeople.city IN (‘London’, ‘Barcelona’);

11. More Complex Uses of Joins (cont’d) • UNION JOINs • SELECT a.snum, a.sname, b.cnum, b.cname FROM Salespeople a UNION JOIN Customers b WHERE a.city = ‘London’ OR b.city = ‘London’; • INNER JOINs • It is a join that excludes no unmatched rows from either table. Opposite of OUTER JOINs. • Syntax: table A [NATURAL] [INNER] JOIN table B [{ON predicate} | {USING column list}]; • Example: SELECT onum, odate, amt, o.snum, sname FROM Salespeople s NATURAL INNER JOIN Orders o;

12. More Complex Uses of Joins (cont’d) • OUTER JOINs • Join that includes unmatched rows from either or both of the joined tables. • Say that the first table name is table A, and the second table name is table B. • LEFT OUTER JOINs It includes all rows from table A, matched or not, plus the matching values from table B if applicable. • RIGHT OUTER JOINs It includes all rows from table B, matched or not, plus the matching values from table A if applicable. • FULL OUTER JOINs It is a combination of LEFT and RIGHT OUTER. All rows from both tables are shown, merged where matches are found.

13. End of Querying Multiple Tables Thank you