1 / 13

Querying Multiple Tables Pertemuan 4

Querying Multiple Tables Pertemuan 4. Matakuliah : T0413/Current Popular IT II Tahun : 2007. AGENDA: •Joining Tables •Joining a Table to Itself •More Complex Uses of Joins. Book: Mastering SQL by Martin Gruber Sybex (2000) Chapter : 10. Joining Tables.

lotta
Download Presentation

Querying Multiple Tables Pertemuan 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related