ANSI JOIN Syntax
Introduction
As of Oracle 9i, Oracle started to support the ANSI JOIN syntax which is part of the ANSI SQL standard. Basically, it's an alternative mechanism for specifying
joins from one set to another, but it has many advantages, readability being one of the most important.
Another major advantage is that, for most cases, you can't NOT specify a predicate (which, as we all know,
is all too easy to do with the "old" syntax).
INNER JOIN
INNER JOINs are a direct join between one or more attributes, i.e.
SQL> CREATE TABLE t1 ( a VARCHAR2(10), b VARCHAR2(10) );
Table created.
SQL> CREATE TABLE t2 ( a VARCHAR2(10), b VARCHAR2(10), c VARCHAR2(10) );
Table created.
SQL> INSERT INTO t1 VALUES ( 'x', 'y' );
1 row created.
SQL> INSERT INTO t2 VALUES ( 'x', 'y', 'z' );
1 row created.
SQL> SELECT *
2 FROM t1
3 INNER JOIN t2 ON ( t1.a = t2.a
4 AND t1.b = t2.b );
A B A B C
---------- ---------- ---------- ---------- ----------
x y x y z
1 row selected.
which is the equivalent of :
SQL> SELECT *
2 FROM t1, t2
3 WHERE t1.a = t2.a
4 AND t1.b = t2.b;
A B A B C
---------- ---------- ---------- ---------- ----------
x y x y z
There is an alternative syntax using the USING clause, which allows you to join with identically named
columns, i.e.
SQL> SELECT *
2 FROM t1
3 INNER JOIN t2 USING ( a, b )
4 /
A B C
---------- ---------- ----------
x y z
1 row selected.
But, notice the difference in output. It's because, when the USING clause is specified, the columns
specified in the USING clause can only be referenced once, i.e. in the above case, "a" and "b" become
sort of "virtual" columns, and further more, they cannot be aliased, i.e.
SQL> SELECT t1.a
2 FROM t1
3 INNER JOIN t2 USING ( a, b )
4 /
SELECT t1.a
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
LEFT / RIGHT OUTER JOIN
The left and right outer join is the equivalent syntax of Oracle's proprietary (+) syntax.
SQL> INSERT INTO t1 VALUES ( 'a', 'b' );
1 row created.
SQL> SELECT *
2 FROM t1
3 LEFT OUTER JOIN t2 ON ( t1.a = t2.a
4 AND t1.b = t2.b )
5 /
A B A B C
---------- ---------- ---------- ---------- ----------
x y x y z
a b
2 rows selected.
Note, the OUTER can be dropped, since, by definition, LEFT, RIGHT and FULL JOINs MUST be
OUTER joins (it's down to personal preference and readability)
SQL> SELECT *
2 FROM t1
3 LEFT JOIN t2 ON ( t1.a = t2.a
4 AND t1.b = t2.b )
5 /
A B A B C
---------- ---------- ---------- ---------- ----------
x y x y z
a b
2 rows selected.
Note, the RIGHT OUTER JOIN is the "opposite" of the LEFT OUTER JOIN, i.e.
SQL> SELECT *
2 FROM t1
3 RIGHT OUTER JOIN t2 ON ( t1.a = t2.a AND t1.b = t2.b );
A B A B C
---------- ---------- ---------- ---------- ----------
x y x y z
c d e
2 rows selected.
FULL OUTER JOIN
The FULL OUTER JOIN is the main reason why the ANSI JOIN syntax has become the syntax of choice for
SQL queries, simply because this was impossible to achieve prior to this syntax without some
form of use of the UNION operator. but basically, it allows you to outer join on both sides of
the join condition, i.e.
SQL> SELECT *
2 FROM t1
3 FULL OUTER JOIN t2 ON ( t1.a = t2.a
4 AND t1.b = t2.b )
5 /
A B A B C
---------- ---------- ---------- ---------- ----------
x y x y z
a b
c d e
3 rows selected.
"Under the covers", however, it still does a UNION operation (as of 10g), i.e.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM t1
4 FULL OUTER JOIN t2 ON ( t1.a = t2.a
5 AND t1.b = t2.b )
6 /
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86 | 3010 | 4 |
| 1 | VIEW | | 86 | 3010 | 4 |
| 2 | UNION-ALL | | | | |
|* 3 | HASH JOIN OUTER | | 82 | 2870 | 3 |
| 4 | TABLE ACCESS FULL| T1 | 82 | 1148 | 1 |
| 5 | TABLE ACCESS FULL| T2 | 82 | 1722 | 1 |
|* 6 | FILTER | | | | |
| 7 | TABLE ACCESS FULL| T2 | 4 | 84 | 1 |
|* 8 | TABLE ACCESS FULL| T1 | 1 | 14 | 1 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."B"="T2"."B"(+) AND "T1"."A"="T2"."A"(+))
6 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "T1" "T1"
WHERE "T1"."A"=:B1 AND "T1"."B"=:B2))
8 - filter("T1"."A"=:B1 AND "T1"."B"=:B2)
Note: cpu costing is off
24 rows selected.
NATURAL JOIN
The NATURAL JOIN is one of the most controversial elements of the ANSI JOIN syntax. Basically, it allows
you to join ALL identically named columns from one table with another, i.e.
SQL> DESC T1
Name
-------------------------------------------------------------
A
B
SQL> DESC T2
Name
-------------------------------------------------------------
A
B
C
SQL> insert into t1 values ( 'x', 'y' );
1 row created.
SQL> insert into t2 values ( 'x', 'y', 'z');
1 row created.
SQL> SELECT a, t2.c FROM t1 NATURAL JOIN t2;
A C
---------- ----------
x z
1 row selected.
Note, that the same restrictions apply as when using the USING clause in the other JOIN operations.
Identically named columns cannot be aliased, i.e.
SQL> select t1.a from t1 natural join t2;
select t1.a from t1 natural join t2
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
The NATURAL JOIN also applies to outer joins, i.e.
SQL> SELECT * FROM t1 NATURAL LEFT OUTER JOIN t2;
A B C
---------- ---------- ----------
x y
1 row selected.
SQL> SELECT t1.a FROM t1 NATURAL LEFT OUTER JOIN t2;
select t1.a from t1 natural left outer join t2
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SQL> SELECT a FROM t1 NATURAL LEFT OUTER JOIN t2;
A
----------
x
1 row selected.
SQL> SELECT * FROM t1 NATURAL FULL OUTER JOIN t2;
A B C
---------- ---------- ----------
x y
1 row selected.
CROSS JOIN
Produces a cross-product of the two sets, otherwise known as a cartesian product. Note, no
join condition is specified, i.e.
SQL> INSERT INTO t1 VALUES ( 'a', 'b' );
1 row created.
SQL> INSERT INTO t1 VALUES ( 'x', 'y' );
1 row created.
SQL> INSERT INTO t2 VALUES ( 'x', 'y', 'z' );
1 row created.
SQL> SELECT * FROM t1 CROSS JOIN t2;
A B A B C
---------- ---------- ---------- ---------- ----------
x y x y z
a b x y z
2 rows selected.