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
- OCA Oracle Database 11g / SQL Fundamentals I Exam Guide (Exam 1Z0-051) – Chapter 7: Displaying Data from Multiple Tables
- http://en.wikipedia.org/wiki/Join_%28SQL%29
- http://blog.codinghorror.com/a-visual-explanation-of-sql-joins
5. Extra
Try Deskbright‘s free SQL interview practice questions to put your SQL knowledge to the test.