1 / 11

Advance Queries (cont.)

Learn about outer joins in database queries, including LEFT JOIN, RIGHT JOIN, FULL JOIN, and their implications on result sets. Understand the order of join, potential pitfalls, and union operations. Dive into reflexive joins and natural joins.

cbramlett
Download Presentation

Advance Queries (cont.)

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. Advance Queries (cont.) INFSY 445 Fall 2005

  2. Outer Joins • Describes what happens when values in one table do not exist in the second table • LEFT JOIN • RIGHT JOIN • FULL JOIN

  3. Outer Joins • LEFT JOIN • All rows in left table displayed • When no matching value in table on right, NULL values inserted into output • RIGHT JOIN works same except right table takes priority

  4. Outer Joins • Caution – joining 2 large tables could produce meaningless results • Order of join causes different results • *= is symbol for OUTER JOIN in databases not SQL 92 compliant

  5. Union • Used to join rows of data from similar tables • The data from both table must match! SELECT employeeID, name, salary, ‘East’ AS Office FROM EmployeeEast UNION SELECT employeeID, name, salary, ‘West’ AS Office FROM EmployeeWest;

  6. Union • Basic command eliminates duplicate rows • To keep duplicates, use UNION ALL

  7. Union / Except / Intersect

  8. Reflexive Join • Join a table to itself • An employee has one boss; a boss can have many employees.

  9. Reflexive Join • What would the tables look like?

  10. Reflexive Join • SELECT Employee.number, Employee.Fname, Employee.manager, E2.Fname • FROM Employee INNER JOIN Employee AS E2 • ON Employee.manager = E2.number;

  11. Joining Tables • NATURAL JOIN • Automatically join 2 tables with a commonly named and defined column • SELECT title, pubid, name • FROM publisher NATURAL JOIN books;

More Related