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:
|0||OS CPU Qt wait time||1||576270482||271||0|
|3||opened cursors cumulative||1||85052502||271||12490|
|4||opened cursors current||1||2301954928||271||7|
|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
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!
+ 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
- NATURAL INNER JOIN – the default when using just “NATURAL JOIN”
- NATURAL LEFT OUTER JOIN – to do a LEFT join using the NATURAL clause
- NATURAL RIGHT OUTER JOIN – to do a RIGHT join using the NATURAL clause