Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Equality Joins

In an equality join, the tables are related using the equals (=) sign. This is the most common join and was used in the example at the beginning of this section.  Basically, an equal join will compare the related values and return only those joined rows where they are the same.

SQL> select
  2    author_last_name,
  3    book_title
  4  from
  5    author a join book_author ba on (a.author_key = ba.author_key)
  6             join book b         on (ba.book_key = b.book_key);

AUTHOR_LAST_NAME              BOOK_TITLE
----------------------------- ------------------------------------hester                        windows success
jones                         windows success
jeckle                        piano greats
weaton                        piano greats
withers                       DOS for dummies
shagger                       The zen of auto repair
smith                         zero loss finance
jeckle                        operations research theory
shagger                       non violins in the workplace
weaton                        UNIX for experts
hester                        pay no taxes and go to jail
jeckle                        the fall of microsoft
shagger                       writers market
smith                         managing stress
shagger                       bears are people too
weaton                        reduce spending the republican way
petty                         the willow weeps no more
hester                        oracle9i sql tuning
shagger                       was george washington feeble?
jones                         was george washington feeble?
jones                         cooking light
jones                         never eat meat
withers                       never eat meat
weaton                        never eat meat
shagger                       how to housebreak your horse

25 rows selected.

Remember that a book can have more than one author.

One ANSI join that is a type of equality joinis the natural join.  A natural join is where tables are joined by matching column names and column values.  As stated earlier, I do not like this join because it allows the database to determine the matching columns.  To me, it is ambiguous to the code reader.  Here is the query above rewritten using a natural join.

select
  author_last_name,
  book_title
from
  author natural join book_author natural join book;

A problem with the natural join is that I can’t use aliases because they change the column names.  The below query will execute correctly without problem.

select
   author_last_name,
   book_title,
   book_key
from
   author natural join book_author
          natural join book;

Using the old Oracle join format, the query above will throw an error stating that book_key is ambiguous because it is in both the BOOK and book_author table.  Using the ANSIformat, the database knows that it equal joined the tables using book_key, so in the rows returned, book_key is the same in both tables.  If I try to alias book_key, I get an error.

SQL> select
  2    a.author_last_name,
  3    b.book_title,
  4    ba.book_key
  5  from
  6    author a natural join book_author ba
  7             natural join book b;
 

  ba.book_key  *
ERROR at line 4:
ORA-00904: "BA"."BOOK_KEY": invalid identifier

My alias changed the column name for the book_key column and confused the database.

What happens when my tables contain columns that match in name but are unsuitable for the join?  An example would be a comment column.  This column contains text comments and I do not want it in the join.  The natural join, however, will use it in the join, unless I exclude it using the USING clause.

select
  author_last_name,
  book_title
from
  author join book_author using (author_key)
         join book        using (book_key);

Notice that the natural key word is no longer used, and in fact, I am no longer doing a natural join but a standard inner join.  The USINGclause is added to define the column or columns used to join the tables.  Columns not listed are excluded from the join.  The USING clause requires that the column names in both tables match.  If not, you must use the ON clause and define the equality.

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.