Oracle – Join Tables Without Stating Any Join Columns – NATURAL JOIN

We saw how using USING clause in the joins simplifies the JOIN conditions. We use it like this

SELECT NAME, e.isdefault, e.VALUE, p.description
    FROM v$ses_optimizer_env e LEFT OUTER
         JOIN v$parameter p USING (NAME)
         JOIN v$session s USING (SID)
   WHERE s.audsid = USERENV ('sessionid')
        AND e.name LIKE '%parallel%'
   ORDER BY isdefault, NAME;

NATURAL JOIN clause:

Today, let us take a  look at another less known join clause – NATURAL JOIN. All it does is on the two tables/views that are joined, it looks for all the matching column names (by column name) and joins on those. We are saved from having to type the list of all columns on which to join.

Let us consider an example

SELECT *
FROM v$statname
NATURAL JOIN v$mystat

which produces the following results:

STATISTIC# NAME CLASS STAT_ID SID VALUE
0 OS CPU Qt wait time 1 576270482 271 0
1 logons cumulative 1 2666645286 271 1
2 logons current 1 3080465522 271 1
3 opened cursors cumulative 1 85052502 271 12490
4 opened cursors current 1 2301954928 271 7
5 user commits 1 582481098 271 0
6 user rollbacks 1 3671147913 271 0
7 user calls 1 2882015696 271 900
8 recursive calls 1 2656001462 271 18945
9 recursive cpu usage 1 4009879262 271 1131
10 pinned cursors current 1 2771133180 271 4

Note that it did not list the common column STATISTIC# twice, even though “SELECT *” was used. It is simple and it saved me from having to type the following lengthier version of the same SQL since we know for a fact that the only matching column name between the two views is the one we want to join on.

SELECT a.*, b.sid, b.value
FROM v$statname a
   INNER JOIN v$mystat b
      ON a.statistic# = b.statistic#

The following table has the structures of the two views used

v$statname V$mystat
STATISTIC#

NAME

CLASS

STAT_ID

SID

STATISTIC#

VALUE

When NATURAL JOIN goes bad:

Although it shortens the join clause, there are consequences. Please read the warning below. I would not recommend using this for something other than quick checks on tables you know really well.

WARNING: People term this feature as “EVIL”!

Unlike the USING clause, before you start using NATURAL JOIN in PL/SQL code, consider the ramifications of doing so, as code that works today may will work differently tomorrow when someone adds/removes columns in views/tables that would in turn result in more/less qualifying “join” columns all of a sudden, in existing queries that were never touched. This feature is ideally recommended for on the fly, free-hand SQL to save a  few key-strokes. If you are curious, you may read this article that terms NATURAL JOIN as all out evil with valid reasons as to why.

Consider these columns in the three views used in the SQL above. We already see an overlap of the column “VALUE” in v$ses_optimizer_env and v$parameter on which we do not want to join (we only want to join on NAME). In this case, using the NATURAL JOIN will produce bad results!

v$ses_optimizer_env V$parameter V$session
****SID ****

ID

****NAME****

SQL_FEATURE

ISDEFAULT

****VALUE****

NUM

****NAME****

TYPE

****VALUE****

DISPLAY_VALUE

ISDEFAULT

ISSES_MODIFIABLE

ISSYS_MODIFIABLE

ISINSTANCE_MODIFIABLE

ISMODIFIED

ISADJUSTED

ISDEPRECATED

ISBASIC

DESCRIPTION

UPDATE_COMMENT

HASH

SADDR

****SID****

SERIAL#

AUDSID

PADDR

USER#

USERNAME

COMMAND

OWNERID

TADDR

LOCKWAIT

STATUS

SERVER

SCHEMA#

SCHEMANAME

+ a lot more addl. columns

When to use?

Use it when it is simple and straightforward in a free-hand SQL used on a one-time basis. Above, it saved me the trouble of having to type the column names to make sure there are no duplicate columns in the SELECTed list and also the agony (not really, but the dramatization helps) of spelling out the join columns. The above is a simple example but when you have master tables that have 5 columns as primary keys with corresponding columns as foreign keys in detail tables, this is a real life saver (with the big assumption that only the column names of 5 PK columns match between the two tables).

Also, note that the NATURAL JOIN can be

  1. NATURAL INNER JOIN – the default when using just “NATURAL JOIN”
  2. NATURAL LEFT OUTER JOIN – to do a LEFT join using the NATURAL clause
  3. NATURAL RIGHT OUTER JOIN – to do a RIGHT join using the NATURAL clause

References:

http://docs.oracle.com/javadb/10.8.2.2/ref/rrefsqljnaturaljoin.html
http://en.wikipedia.org/wiki/Join_(SQL)
http://gplivna.blogspot.com/2007/10/natural-joins-are-evil-motto-if-you.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s