Oracle – Simplify Your Joins – Using “USING” In JOIN Clause?!

Same SQL written in 3 different ways

“There are a thousand ways to skin a cat” or so they say. Let us look at just three ways of writing the same SQL and build the case for using the USING clause.

The query below is to get the current Oracle session’s effective “parallel” related parameter settings (could be different from system settings). All of the following SQL try to do the exact same thing (listed in order of least desirability):

Deprecated outer join syntax using (+):

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

This is the least desirable for several reasons

Reason #1:

It does not work. It produces the following error:

[Error] Execution (27: 15): ORA-01417: a table may be outer joined to at most one other table

There is no point in spending time analyzing the error as it is obvious and more over, Oracle does not want you to use this syntax anymore.

Reason #2:

One has to be careful when adding WHERE clause conditions on columns of outer-joined tables as it turns the query into INNER join based on the condition used. Again, this query will not even work!

Reason #3:

The WHERE clause is very cluttered and is not easy to follow

2. ANSI outer join

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

This syntax is the recommended ANSI syntax but we could simplify this some more.

3. ANSI outer join using “USING”

How to join on a single column?

The column that is used to join on is in both joined tables with the same column name making the syntax simpler and the code more readable

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;

This syntax is the simplest of the three not to mention again that it is very concise.

How to join on multiple columns?

If the join had to be on more than one column, I could have just as easily said
USING (SID, SERIAL#)


         JOIN v$session s USING (SID, SERIAL#)

Dissecting the USING clause:

You would be better off reading the documentation than me trying to explain the USING clause (especially on how it relates to using * in the SELECT column list).

References:

http://docs.oracle.com/javadb/10.8.1.2/ref/rrefsqljusing.html
http://www.basswoodtech.com/scripts/Ch07/ses_optimizer.sql