Advance Queries cont INFSY 445 Fall 2005 Outer

Advance Queries (cont. ) INFSY 445 Fall 2005

Outer Joins n Describes what happens when values in one table do not exist in the second table 4 LEFT JOIN 4 RIGHT JOIN 4 FULL JOIN

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

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

Union n Used to join rows of data from similar tables n The data from both table must match! SELECT employee. ID, name, salary, ‘East’ AS Office FROM Employee. East UNION SELECT employee. ID, name, salary, ‘West’ AS Office FROM Employee. West;

Union n Basic command eliminates duplicate rows n To keep duplicates, use UNION ALL

Union / Except / Intersect T 1 UNION T 2 T 1 INTERSECT T 2 A+B+C B T 1 EXCEPT T 2 A

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

Reflexive Join n What would the tables look like?

Reflexive Join SELECT Employee. number, Employee. Fname, Employee. manager, E 2. Fname FROM Employee INNER JOIN Employee AS E 2 ON Employee. manager = E 2. number;

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