1. Inner join, self join and Outer join Sen Zhang

2. Joining data together is one of the most significant strengths of a relational database. • A join is a query that combines rows from two or more relations. • Joins allow database users to combine data from one table with data from one or more other tables or views, or synonyms, as long as they are relations.

3. Tables are “joined” two at a time making a new relation (a table generated on the fly) containing all possible combinations of rows from the original two tables (sometimes called a “cross join” or “Cartesian product”). • See sample script

4. A join condition is usually used to limit the combinations of table data to just those rows containing columns that match columns in the other table. • Most joins are “equi-joins” where the data from a column in one table exactly matches data in the column of another table.

5. It is also possible (though usually less efficient) to join using ranges of values or other comparisons between the tables involved. • A table may be “joined” to another table, tables, or even itself (reused multiple times).

6. It is important to understand that whenever two or more tables/views/synonyms (in fact, they are all relations) are listed in a FROM clause, a join results. • Join conditions serve the purpose of limiting the number of rows returned by the join. • The absence of a join condition results in all possible combinations of rows from the involved tables, i.e. a Cartesian product, which is usually not useful information.

7. Inner Joins • An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

8. Inner Join • Traditional inner joins look for rows that match rows in the other table(s), i.e. to join two tables based on values in one table being equal to values in another table • Also known as equality join, equijoin or natural join • Returns results only if records exist in both tables

9. Joining Via Linking Table

10. Self-Join • A query that joins a table to itself, for example, employee table can be joined to itself to find out subordinate - supervisor pairs. • Used when a table has a foreign key relationship to itself (usually parent-child relationship) • Must create a table alias and structure the query as if you are joining the table to a copy of itself • FROM table1 alias1, ... • Use alias, not table name for select and where clauses

11. Self-Join Example

12. From inner join to outer join • A problem with the simple inner join is that only rows that match between tables are returned; while it is also possible that a user might be interested in rows that DO NOT match rows in the other table(s). • Finding rows without matches is often referred as Outer Join.

13. What is an outer join • An outer join extends the result of a simple join (inner join, equ-join, theta join or natural join). • An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join.

14. What is an outer join • To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. • For all rows in A that have no matching rows in B, Oracle returns NULL for any select list expressions containing columns of B. 

15. Outer join in Oracle 8i • The syntax for performing an outer join in SQL is database-dependent. • For example, in Oracle 8i, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows. • In Oracle 9i, another format is used.

16. Outer Join • Returns all rows in one table and matching rows in joined table • Inner table: all rows are returned, in other word, if all rows need to be returned for one table, that table is called inner table. • Outer table: matching rows are returned • Outer table marked with a + in join condition inner_table.join_field = outer_table.join_field(+) Null values are inserted for fields in outer table that are not found

17. How to interpret “(+)”?Which table (+) should tail? • The oracle outer join operator “(+)” simply tells SQL engine which table to generate null values for the unmatched row of the opposite table to match the table. • Or simply, which side should generate null rows.

18. Full join • How about if we want to include rows in both tables that cannot find match in the opposite tables? It is called full join. • Until now, the only way to accomplish a full join (values missing on both sides of a query) was to Union the results of both left outerjoin and right outerjoin.

19. Sample scripts in class • See sample scripts distributed in class for various outer joins