 |
|
Oracle Tips by Burleson |
Non-Equality Joins
A non-equality joinis not
based on the equals (=) sign. Sometimes called an anti-join,
non-equality joins include those joins based on <>, <, > and NOT.
select
store_name,
order_number
from
store join sales on (store.store_key <> sales.store_key);
This example gives us the Cartesian join minus the
equal join, thus the name anti-join. Remember, the non-equal join is
talking about joining the tables, not filtering the row, that is still
the job of the WHERE clause.
Outer Joins
With the equal join, you get all the rows where
the comparison is equal. Sometimes, you want all the rows in one
table and the matching rows in the other table.
For example, if I list my authors and the books
they have written, I get the results below.
SQL> select
2 author_last_name,
3 book_key
4 from
5 author join book_author using (author_key)
6 order by author_last_name;
AUTHOR_LAST_NAME BOOK_K
---------------------------------------- ------hester
B101
hester B109
hester B116
jeckle B102
jeckle B110
jeckle B106
jones B101
jones B119
jones B117
jones B118
petty B115
shagger B104
shagger B111
shagger B113
shagger B120
shagger B117
shagger B107
smith B105
smith B112
weaton B102
weaton B108
weaton B114
weaton B119
withers B103
withers B119
25 rows
selected.
The problem with this listing is that there are
ten authors in the author table and only eight listed. The remaining
two authors have not yet written a book.
What if I wanted the listing to include these two
authors? Because they do not match the equal join, I will need to use
an outer join. An outer join will include all rows from the outer
table and those matching rows from the other table. Rows that are
included from the outer table that have no match in the other table
will have NULLs in those columns.
SQL> select
2 author_last_name,
3 book_key
4 from
5 author left outer join book_author using (author_key)
6 order by author_last_name;
AUTHOR_LAST_NAME BOOK_K
---------------------------------------- ------
clark
hester B101
hester B109
hester B116
jeckle B102
jeckle B110
jeckle B106
jones B101
jones B119
jones B117
jones B118
mee
petty B115
shagger B104
shagger B111
shagger B113
shagger B120
shagger B117
shagger B107
smith B105
smith B112
weaton B102
weaton B108
weaton B114
weaton B119
withers B103
withers B119
27 rows
selected.
In the example above, the AUTHOR table is on the
left, and we are using a left outer join, so we get all the rows in
the AUTHOR table and the matching rows in the
book_author table. Notice
that both authors clark and mee now are listed, and the
book_key column is NULL. In
the standard Oracle format, outer joins can be confusing. Below is
the same query in the standard Oracle format.
select
author_last_name,
book_key
from
author,
book_author
where
author.author_key = book_author.author_key(+)
order by author_last_name;
Notice the (+) in the WHERE clause. This
indicates a left outer join. If we were using a right outer join, the
WHERE clause would be:
author.author_key(+) = book_author.author_key
Here, all the rows from the
book_author table would be
included and the missing rows from the AUTHOR table would be NULL.
The example below demonstrates the ANSIright outer
join.
SQL> select
2 author_last_name,
3 book_key
4 from
5 author right outer join book_author using (author_key)
6 order by author_last_name;
AUTHOR_LAST_NAME BOOK_K
---------------------------------------- ------hester
B116
hester B109
hester B101
jeckle B110
jeckle B102
jeckle B106
jones B119
jones B118
jones B117
jones B101
petty B115
shagger B120
shagger B111
shagger B117
shagger B113
shagger B107
shagger B104
smith B112
smith B105
weaton B119
weaton B114
weaton B108
weaton B102
withers B119
withers B103
B122
B121
27 rows
selected.
Here we see two planned books that do not yet have
authors; book_keys 121 and
122.
Sometimes, you want to include all rows in both
tables. The ANSISQL format can do this using the full outer join.
SQL> select
2 author_last_name,
3 book_key
4 from
5 author full outer join book_author using (author_key)
6 order by author_last_name;
AUTHOR_LAST_NAME BOOK_K
---------------------------------------- ------
clark
hester B101
hester B109
hester B116
jeckle B102
jeckle B110
jeckle B106
jones B101
jones B119
jones B117
jones B118
mee
petty B115
shagger B104
shagger B111
shagger B113
shagger B120
shagger B117
shagger B107
smith B105
smith B112
weaton B102
weaton B108
weaton B114
weaton B119
withers B103
withers B119
B122
B121
29 rows
selected.
Notice that the results included the unpublished
authors (clark and mee) and the books not yet assigned to authors
(B121 and B122). There is no standard Oracle format for a full outer
join. You must union a left and right outer join to get the same
results.
SQL> select
2 author_last_name,
3 book_key
4 from
5 author,
6 book_author
7 where
8 author.author_key = book_author.author_key(+)
9 union
10 select
11 author_last_name,
12 book_key
13 from
14 author,
15 book_author
16 where
17 author.author_key(+) = book_author.author_key
18 order by author_last_name;
AUTHOR_LAST_NAME BOOK_K
---------------------------------------- ------
clark
hester B101
hester B109
hester B116
jeckle B102
jeckle B106
jeckle B110
jones B101
jones B117
jones B118
jones B119
mee
petty B115
shagger B104
shagger B107
shagger B111
shagger B113
shagger B117
shagger B120
smith B105
smith B112
weaton B102
weaton B108
weaton B114
weaton B119
withers B103
withers B119
B121
B122
29 rows
selected.
Notice that the union removed duplicate rows, and
I only ordered the results set once at the end of the query.
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by
John Garmany
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
