Some time ago, I had to give a fundamentals course on the Oracle SQL language. An important topic within that course is the joining of tables. What I was missing in the course book was a clear demonstration on the differences between the traditional Oracle join syntax and the ANSI SQL join syntax. In this blog post, I’ll make a comparison between the two join syntaxes. On a side note, there is no difference in terms of performance between both syntaxes.

1. Syntax overview

-- ANSI SQL syntax
select table1.column,
       table2.column
from table1
[natural join table2] |
[join table2 using (column)] |
[join table2 on (table1.column = table2.column)] |
[left | right | full [outer] join table2 on (table1.column = table2.column)] |
[cross join table2];

-- traditional Oracle syntax
select table1.column,
       table2.column
from table1,
     table2
[where table1.column    = table2.column] |
[where table1.column(+) = table2.column] |
[where table1.column    = table2.column(+)];

2. The EMP and DEPT demo tables

In the upcoming sections, I’ll demonstrate the syntactic differences between the various join techniques by providing example queries on two well-known demo tables: EMP and DEPT. The DDL and DML code for these two tables can be found in one of my previous blog posts: The EMP and DEPT tables in Oracle

2.1. The EMP table

  • Primary key column: EMPNO
  • Foreign key relationship with the DEPT table: DEPTNO
  • The MGR column contains the EMPNO of the employee’s manager
  • There is just one EMP record with no department assigned: KING
desc emp;

Name     Null     Type
-------- -------- ------------
EMPNO    NOT NULL NUMBER(4)
ENAME             VARCHAR2(10)
JOB               VARCHAR2(9)
MGR               NUMBER(4)
HIREDATE          DATE
SAL               NUMBER(7,2)
COMM              NUMBER(7,2)
DEPTNO            NUMBER(2)

select empno, ename, job, mgr, deptno
from emp;

EMPNO ENAME      JOB        MGR DEPTNO
----- ---------- --------- ---- ------
 7839 KING       PRESIDENT
 7698 BLAKE      MANAGER   7839     30
 7782 CLARK      MANAGER   7839     10
 7566 JONES      MANAGER   7839     20
 7788 SCOTT      ANALYST   7566     20
 7902 FORD       ANALYST   7566     20
 7654 MARTIN     SALESMAN  7698     30
 7934 MILLER     CLERK     7782     10

8 rows selected

2.2. The DEPT table

  • Primary key column: DEPTNO
  • There is just one department with no employees assigned: OPERATIONS
desc dept;

Name   Null     Type
------ -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME           VARCHAR2(14)
LOC             VARCHAR2(13)

select *
from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

4 rows selected

3. Join techniques

3.1. Inner joins

An inner join requires each record in the two joined tables to have matching records.

3.1.1. The NATURAL JOIN clause

The NATURAL JOIN clause implicitly joins the tables on the columns with common names in table A and table B. In the below example, the query performs a natural join between the EMP and DEPT tables, thus joining on the common column DEPTNO.

-- ANSI SQL syntax
select emp.ename,
       dept.dname
from emp
natural join dept;

-- traditional Oracle syntax
select emp.ename,
       dept.dname
from emp,
     dept
where emp.deptno = dept.deptno;

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
CLARK      ACCOUNTING
JONES      RESEARCH
FORD       RESEARCH
SCOTT      RESEARCH
MARTIN     SALES
BLAKE      SALES

7 rows selected

Best Practice: Avoid the NATURAL JOIN syntax and use the JOIN ON instead.

3.1.2. The natural JOIN USING clause

With the JOIN USING clause you can explicitly specify the common columns to join.

-- ANSI SQL syntax
select emp.ename,
       dept.dname
from emp
join dept using (deptno);

-- traditional Oracle syntax
select emp.ename,
       dept.dname
from emp,
     dept
where emp.deptno = dept.deptno;

-- produces the same result as the NATURAL JOIN

Best Practice: Avoid the JOIN USING syntax and use the JOIN ON instead.

3.1.3. The natural JOIN ON clause

The NATURAL JOIN and JOIN USING clauses always depend on join columns with identical names. The JOIN ON clause is more flexible as it allows you to explicitly specify the join columns, regardless of their column names.

-- ANSI SQL syntax
select emp.ename,
       dept.dname
from emp
join dept on (emp.deptno = dept.deptno);

-- traditional Oracle syntax
select emp.ename,
       dept.dname
from emp,
     dept
where emp.deptno = dept.deptno;

-- produces the same result as the NATURAL JOIN and the JOIN USING

Joining a table to itself (self join) using the JOIN ON clause:

-- ANSI SQL syntax
select emp2.ename as employee,
       emp1.ename as manager
from emp emp1
join emp emp2 on (emp1.empno = emp2.mgr);

-- traditional Oracle syntax
select emp2.ename as employee,
       emp1.ename as manager
from emp emp1,
     emp emp2
where emp1.empno = emp2.mgr;

EMPLOYEE   MANAGER
---------- ----------
SCOTT      JONES
FORD       JONES
MARTIN     BLAKE
MILLER     CLARK
JONES      KING
CLARK      KING
BLAKE      KING

7 rows selected

3.2. Outer joins

An outer join does not require each record in the two joined tables to have a matching record.

3.2.1. Left outer join

The result of a LEFT OUTER JOIN (or simply left join) for tables A and B always contains all records of the left table (A), even if the join condition does not find any matching record in the right table (B).

-- ANSI SQL syntax
select emp.ename,
       dept.dname
from emp
left outer join dept on (emp.deptno = dept.deptno);

-- traditional Oracle syntax
select emp.ename,
       dept.dname
from emp,
     dept
where emp.deptno = dept.deptno(+);

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
SCOTT      RESEARCH
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
KING

8 rows selected
3.2.2. Right outer join

The result of a RIGHT OUTER JOIN (or simply right join) for tables A and B always contains all records of the right table (B), even if the join condition does not find any matching record in the left table (A).

-- ANSI SQL syntax
select emp.ename,
       dept.dname
from emp
right outer join dept on (emp.deptno = dept.deptno);

-- traditional Oracle syntax
select emp.ename,
       dept.dname
from emp,
     dept
where emp.deptno(+) = dept.deptno;

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
CLARK      ACCOUNTING
JONES      RESEARCH
SCOTT      RESEARCH
FORD       RESEARCH
MARTIN     SALES
BLAKE      SALES
           OPERATIONS

8 rows selected
3.2.3. Full outer join

A FULL OUTER JOIN combines the effect of applying both left and right outer joins. It produces the set of all records in table A and table B, with matching records from both sides where available. If there is no match, the missing side will contain NULL.

-- ANSI SQL syntax
select emp.ename,
       dept.dname
from emp
full outer join dept on (emp.deptno = dept.deptno);

-- a FULL OUTER JOIN query does not exist in traditional Oracle syntax

ENAME      DNAME
---------- --------------
KING
BLAKE      SALES
CLARK      ACCOUNTING
JONES      RESEARCH
SCOTT      RESEARCH
FORD       RESEARCH
MARTIN     SALES
MILLER     ACCOUNTING
           OPERATIONS

9 rows selected

3.3. Cross join

A CROSS JOIN returns the Cartesian product of rows from tables A and B. It will produce rows which combine each row from the table A with each row from table B.

-- ANSI SQL syntax
select emp.ename,
       dept.dname
from emp
cross join dept;

-- traditional Oracle syntax
select emp.ename,
       dept.dname
from emp,
     dept;

-- skipped the result set

32 rows selected

4. Resources

5. Extra

Try Deskbright‘s free SQL interview practice questions to put your SQL knowledge to the test.

Advertisements