1 / 9

Special Joins

Special Joins. Week 4. Objective. Write SELECT statements to display left, right and full outer joins. Outer Joins. Inner join displays only matching rows Use LEFT OUTER JOIN to display all matching rows and include all unmatched rows from table on left side of JOIN

dillon-hooper
Download Presentation

Special Joins

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. Special Joins Week 4

  2. Objective • Write SELECT statements to display left, right and full outer joins

  3. Outer Joins • Inner join displays only matching rows • Use LEFT OUTER JOIN to display all matching rows and include all unmatched rows from table on left side of JOIN • Use RIGHT OUTER JOIN to display all matching rows and include all unmatched rows from table on right side of JOIN • Use FULL OUTER JOIN to display all matching rows and include all unmatched rows from both tables

  4. Employees Table • EMP_ID LAST_NAME DEPT_ID • Whalen10 • Hartstein20 • Fay20 • 124Mourgos50 • 141Rajs 50 • 142Davies50 • 143Matos50 • 144 Vargas50 • 103Hunold60 • 104Ernst60 • 107Lorentz60 • 149Zlotkey80 • 174Higgins 80 • 176 Taylor80 • 100 King90 • 101Kochhar90 • 102De Haan 90 • 205 Higgins110 • 206Gietz 110 • 178 Grant NOTE: Employee 178 not in a dept • 20 rows selected.

  5. Departments Table DEPARTMENT_ID DEPARTMENT_NAME 10Administration 20Marketing 50Shipping 60IT 80Sales 90Executive 110Accounting 190Contracting NOTE: dept 190 has no employees 8 rows selected.

  6. Employees INNER JOIN Departments • EMP_ID LAST_NAME DEPT_IDDEPT_NAME • Whalen10 Administration • Hartstein20Marketing • Fay20Marketing • 124Mourgos50 Shipping • 141Rajs 50Shipping • 142Davies50Shipping • 143Matos50Shipping • 144 Vargas50Shipping • 103Hunold60IT • 104Ernst60IT • 107Lorentz60IT • 149Zlotkey80Sales • 174Higgins 80Sales • 176 Taylor80Sales • 100 King90Executive • 101Kochhar90Executive • 102De Haan 90Executive • 205 Higgins110Accounting • 206Gietz 110Accounting • 19 rows selected.

  7. Employees LEFT OUTER JOIN Departments • EMP_ID LAST_NAME DEPT_IDDEPT_NAME • Whalen10 Administration • Hartstein20Marketing • Fay20Marketing • 124Mourgos50 Shipping • 141Rajs 50Shipping • 142Davies50Shipping • 143Matos50Shipping • 144 Vargas50Shipping • 103Hunold60IT • 104Ernst60IT • 107Lorentz60IT • 149Zlotkey80Sales • 174Higgins 80Sales • 176 Taylor80Sales • 100 King90Executive • 101Kochhar90Executive • 102De Haan 90Executive • 205 Higgins110Accounting • 206Gietz 110Accounting • 178Grant • 20 rows selected. NOTE: Employee 178 is now displayed!

  8. Employees RIGHT OUTER JOIN Departments • EMP_ID LAST_NAME DEPT_IDDEPT_NAME • Whalen10 Administration • Hartstein20Marketing • Fay20Marketing • 124Mourgos50 Shipping • 141Rajs 50Shipping • 142Davies50Shipping • 143Matos50Shipping • 144 Vargas50Shipping • 103Hunold60IT • 104Ernst60IT • 107Lorentz60IT • 149Zlotkey80Sales • 174Higgins 80Sales • 176 Taylor80Sales • 100 King90Executive • 101Kochhar90Executive • 102De Haan 90Executive • 205 Higgins110Accounting • 206Gietz 110Accounting • 190Contracting • 20 rows selected. NOTE: Department 190 is now displayed!

  9. Employees FULL OUTER JOIN Departments • EMP_ID LAST_NAME DEPT_IDDEPT_NAME • Whalen10 Administration • Hartstein20Marketing • Fay20Marketing • 124Mourgos50 Shipping • 141Rajs 50Shipping • 142Davies50Shipping • 143Matos50Shipping • 144 Vargas50Shipping • 103Hunold60IT • 104Ernst60IT • 107Lorentz60IT • 149Zlotkey80Sales • 174Higgins 80Sales • 176 Taylor80Sales • 100 King90Executive • 101Kochhar90Executive • 102De Haan 90Executive • 205 Higgins110Accounting • 206Gietz 110Accounting • 178Grant • 190Contracting • 21 rows selected.NOTE: Emp 178 and Dep 190 both displayed!

More Related