What are ANSI Joins in Oracle?

Oracle supports ANSI Joins since Oracle 9i release. ANSI Joins act as good options to traditional joins provided by Oracle. Also they are easily readable in natural language as compared to conventional Oracle Joins. Both kind of joins are interesting to learn but if you are new to Oracle, it is better to practice more with ANSI joins.

Types of ANSI Joins

INNER JOIN
NATURAL JOIN
CROSS JOIN
FULL OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN

INNER JOIN

Inner join consists of two clauses

  • USING
  • ON

INNER is optional keyword here. We can use just JOIN clause and perform the same function.

INNER JOIN…USING

The USING clause is used when columns of both the tables have same name.
Note: Qualifiers or aliases cannot be used for the columns mentioned in USING clause.
SELECT e.emp_name, d.dept_name FROM employee e INNER JOIN department d USING (d.dept_no); – This will return error.

Syntax

SELECT...FROM table1 INNER JOIN table2 USING (column1, column2);

Example

SELECT e.emp_name, d.dept_name FROM employee e INNER JOIN department d USING (dept_no);

INNER JOIN…ON

The ON clause is used when columns of both the tables may or may not have same name. Also it does not make sense to use WHERE clause after we use INNER JOIN…ON clause.

Syntax

SELECT...FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1;

Example

SELECT e.emp_name, d.dept_name FROM employee e INNER JOIN department d ON e.dept_no = d.dept_no;

NATURAL JOIN

Natural join is new in ANSI joins. In conventional oracle joins there is no equivalent of natural join. When we use Natural Join Oracle identifies all the columns with same name and join the tables. The only benefit of using this join is that we do not need to specify join predicates. Most of the time it is better to avoid this join.

Syntax

SELECT...FROM table1 NATURAL JOIN table2;

Example

SELECT e.emp_name, d.dept_name FROM employee e NATURAL JOIN department d;

CROSS JOIN

Cross Join is nothing but a Cartesian product. Each row of first table joins with all rows of second table. Equivalent in oracle syntax is the query that has no joins.

Syntax

SELECT...FROM table1 CROSS JOIN table2;

Example

SELECT e.emp_no, d.dept_no FROM employee e CROSS JOIN department d;

LEFT OUTER JOIN

When we use left outer join in traditional oracle syntax we make use of (+) sign. This is bit ambiguous if multiple tables are joined. Unlike traditional oracle join ANSI LEFT OUTER JOIN has directional approach of interpreting tables in the FROM clause from left to right. The LEFT OUTER JOIN returns all the rows from the table on the left side in addition to the common rows in both tables, or NULLS if matching rows doesn’t exist in the right side table.

Syntax

SELECT...FROM table1 LEFT OUTER JOIN table2 ON table1.column1 = table2.column1;

Example

SELECT e.emp_no, d.dept_no FROM employee e LEFT OUTER JOIN department d ON e.dept_no = d.dept_no;

In this example, all the rows of employee table is returned along with the values that is common in both tables, or NULLS if matching rows doesn’t exists in department table.

RIGHT OUTER JOIN

ANSI RIGHT OUTER JOIN is reverse to that of LEFT OUTER JOIN. The directional approach of interpreting tables in the FROM clause is from right to left. The RIGHT OUTER JOIN returns all the rows from the table on the right side in addition to the common rows from both the tables, or NULLS if matching rows doesn’t exist in the left side table.

Syntax

SELECT...FROM table1 RIGHT OUTER JOIN table2 ON table1.column1 = table2.column1;

Example

SELECT e.emp_no, d.dept_no FROM employee e RIGHT OUTER JOIN department d ON e.dept_no = d.dept_no;

In this example, all the rows of department table is returned along with the values that is common in both tables, or NULLS if matching rows doesn’t exists in employee table.

FULL OUTER JOIN

ANSI FULL OUTER JOIN returns data that is common in both tables along with blanks for the data that is mismatch between two tables. The directional approach is left-to-right and right-to-left in full outer join.

Syntax

SELECT...FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column1;

Example

SELECT e.emp_no, d.dept_no FROM employee e FULL OUTER JOIN department d ON e.dept_no = d.dept_no;

In this example, all the data that is common in employee and department tables are returned along with blanks for the data that is uncommon in both tables.

For more information you can read official documentation of  Joins in Oracle here.

Please subscribe here to stay updated on new posts of OracleMine.com .

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.