1 / 16

Multiple Table Queries (Inner Joins, Equijoins)

Multiple Table Queries (Inner Joins, Equijoins). Week 3. Objective. Write SELECT statements to display data from more than one table using equijoins (i.e. equality joins, also known as simple or inner joins). Foreign key. Primary key. Related Tables. DEPARTMENTS. EMPLOYEES.

psyche
Télécharger la présentation

Multiple Table Queries (Inner Joins, Equijoins)

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. Multiple Table Queries (Inner Joins, Equijoins) Week 3

  2. Objective • Write SELECT statements to display data from more than one table using equijoins (i.e. equality joins, also known as simple or inner joins)

  3. Foreign key Primary key Related Tables DEPARTMENTS EMPLOYEES DEPT_NODEPT_NAMELOCN 10 Accounting TORONTO 20 Research OTTAWA 30 Sales Toronto 40 Operations Markham 60 Service Toronto EMP_NOENAME .. DEPT_NO 101 Cohen ... 30 102 Huang ... 30 103 Smith ... 20 . . . 114 Miller ... 10 In queries data from several related tables may often have to be shown. These tables should be related through common data: the primary key of one table is often the foreign key of another table.

  4. Display Data from Related Tables DEPARTMENTS EMPLOYEES DEPT_NODEPT_NAMELOCN 10 Accounting TORONTO 20 Research OTTAWA 30 Sales Toronto 40 Operations Markham 60 Service Toronto EMP_NOENAME .. DEPT_NO 101 Cohen ... 30 102 Huang ... 30 103 Smith ... 20 . . . 114 Miller ... 10 EMP_NO DEPT_NO LOCN ----- ------- -------- 101 30 Toronto 102 30 Toronto 103 20 OTTAWA ... 114 10 TORONTO 14 rows selected. 14 rows of output are to be displayed - one row for each employee

  5. Equijoins • Use an equijoin to display data from each row of one table with a related row in another table • Joins are normally made between primary key columns and related foreign key columns • Use = between related columns to join rows • Join condition(s) specified in WHERE clause • Good to prefix all column names with the table name for clarity and efficiency • Must prefix the column name with the table name when the same column name appears in more than one of the tables

  6. Equijoin Syntax SELECT table1.column1, table1.column3, table2.column4 FROM table1, table2 WHERE table1.column3 = table2.column1 • Assuming that column1 is the primary key of table2 and column 3 in table 1 contains the same data and is a foreign key

  7. Equijoin Example SELECT employees.emp_no, employees.dept_no, departments.locn FROM employees, departments WHERE employees.dept_no = departments.dept_no

  8. What if you forget to use a Join Condition? • If a join condition between tables is omitted in a multi-table query then every row from the first table will be joined with every row from the second table • If table 1 has m rows and table 2 has n rows then result will have m*n rows • Result is called a Cartesian Product • Result is normally meaningless

  9. Cartesian Product Example SELECT employees.emp_no, employees.dept_no, departments.locn FROM employees, departments WRONG…….WRONG……… ( this query is missing a join condition between dept_no of employees table and dept_no of departments table)

  10. Cartesian Product Example Result EMPLOYEES (14 rows) DEPARTMENTS (5 rows) EMP_NOENAME .. DEPT_NO 101 Cohen ... 30 102 Huang ... 30 103 Smith ... 20 . . . 114 Miller ... 10 DEPT_NODEPT_NAMELOCN 10 Accounting TORONTO 20 Research OTTAWA 30 Sales Toronto 40 Operations Markham 60 Service Toronto EMP_NO DEPT_NO LOCN ----- ------- -------- 101 30 TORONTO 102 30 TORONTO 103 20 TORONTO ... 114 10 Toronto 70 rows selected. 70 rows of output are produced but there are only 14 employees, and each employee will be associated with all locations!

  11. How to Avoid Cartesian Product • A Cartesian product is formed when: • A join condition is omitted • A join condition is invalid • All rows in the first table are joined to all rows in the second table • To avoid a Cartesian product, always include a valid join condition in a WHERE clause.

  12. ORD CUSTID ORDID ------- ------- 101 610 102 611 104 612 106 601 102 602 106 604 106 605 ... 21 rows selected. ITEM ORDID ITEMID ------ ------- 610 3 611 1 612 1 601 1 602 1 ... 64 rows selected. Joining More Than Two Tables CUSTOMER NAME CUSTID ----------- ------ JOCKSPORTS 100 TKB SPORT SHOP 101 VOLLYRITE 102 JUST TENNIS 103 K+T SPORTS 105 SHAPE UP 106 WOMENS SPORTS 107 ... ... 9 rows selected. How many join conditions needed? How many rows of output could be produced?

  13. Joining More Than Two Tables SELECT customer.name, ord.ordid, item.ordid, item.itemid FROM customer, ord, item WHERE customer.custid = ord.custid AND ord.ordid = item.ordid

  14. Number of Equijoin Conditions Required • To join n tables together, you need a minimum of (n-1) join conditions. • Therefore, to join three tables, a minimum of two joins are required. • However if data is being related by a concatenated primary key then more than (n-1) joins will be required.

  15. Table Aliases • Use table aliases instead of lengthy table names. • Table aliases are defined in the FROM clause: the table name is specified, followed by a space and then the table alias. • Table alias is only valid for the SELECT statement in which it is defined. • If a table alias is defined in the FROM clause then that table alias must be substituted for the table name throughout the SELECT statement.

  16. Table Alias Example SELECT e.emp_no, e.dept_no, d.locn FROM employees e, departments d WHERE e.dept_no = d.dept_no

More Related