242 Lecture 13 Outerjoin

7/28/2019 242 Lecture 13 Outerjoin

1/19

Inner join, self join and Outer

join

Sen Zhang


2/19

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/19

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/19

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 anothertable.


5/19

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/19

It is important to understand that whenever twoor more tables/views/synonyms (in fact, they areall relations) are listed in a FROM clause, a joinresults.

Join conditions serve the purpose of limiting thenumber of rows returned by the join.

The absence of a join condition results in all

possible combinations of rows from the involvedtables, i.e. a Cartesian product, which is usuallynot useful information.


7/19

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/19

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/19

Joining Via Linking Table


10/19

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 relationshipto 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/19

Self-Join Example


12/19

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 DONOT match rows in the other table(s).

Finding rows without matches is often

referred as Outer Join.


13/19

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 arenot returned by a simple join.


14/19

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 columnsof B.


15/19

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/19

Outer Join

Returns all rows in one table and matching rows injoined table

Inner table: all rows are returned, in other word, ifall 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 thatare not found

H t i t t ( )?


17/19

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/19

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/19

Sample scripts in class

See sample scripts distributed in class for

various outer joins

242 Lecture 13 Outerjoin

Documents

Transcript of 242 Lecture 13 Outerjoin