Oracle Inner Join Syntax - Four different ways

Oracle Inner Join Syntax - Four different ways

I’ve worked with Oracle databases for many years, and during this time I have seen code written by lots of different people. One thing I have noticed is that with few exceptions, people who started working with Oracle many years ago tend to write joins in some way and people newer to the database do it in a different way, so there are 2 predominant syntax's used. However, there are at least 2 more ways to code inner joins which leaves us with 4 different methods to achieve the same thing (not all of them applicable in all cases). Let’s take a look at them.

The Old Syntax:

Example:

SELECT e.employee_id, d.department_name

FROM employee e, department d

WHERE e.department_id = d.department_id

  AND e.salary > 1000;

The New Standard Syntax: The JOIN clause

Example:

SELECT e.employee_id, d.department_name

FROM employee e JOIN department d

  ON e.department_id = d.department_id

WHERE e.salary > 1000;

The JOIN clause is actually part of the FROM clause, so, as you can see, now we have only one condition in the WHERE clause, and we have the join condition as part of the JOIN clause, which for me makes things more understandable. The JOIN keyword is used for different kinds of joins, but the INNER join is the default one, so for inner joins we don’t need to specify the type.

Let’s see an example with more than one join condition and more than 2 tables:

SELECT e.employee_id, dep.department_name, div.division_name

FROM employee e

JOIN department dep

  ON e.department_id = dep.department_id

JOIN division div

  ON dep.division_id = div.division_id

    AND dep.country_id = div.country_id

WHERE e.salary > 1000;

First ANSI variant: The USING clause

The ANSI standard for joins includes a way to write less code when you are writing joins that comply with certain conditions. If the join you are writing is an equi join (I mean, the condition is an equality one) and the columns involved in the join conditions are named the same in both sources (tables, views), then you can use the USING clause as a ‘shorthand’.

The syntax is very simple:

USING (column1 [, column2, ..., column n])

Example:

SELECT e.employee_id, dep.department_name, div.division_name

FROM employee e

JOIN department dep 

  USING (department_id)

JOIN division div

  USING (division_id, country_id)

WHERE e.salary > 1000;

Second ANSI variant: The NATURAL join

The ANSI standard provides another option to write less code if we want to join our tables using all of the columns that have the same name in both sources.

In the case of our previous example, we could use the NATURAL join to get the same results only if the only common column between employee and department were department_id, and the only common columns between department and division were division_id and country_id. If there were other columns with the same name but unmatched data, we would get an empty result set.

So, assuming the necessary condition is met, this is how our example query would look like by using natural joins:

Example:

SELECT e.employee_id, dep.department_name, div.division_name

FROM employee e

NATURAL JOIN department dep 

NATURAL JOIN division div

WHERE e.salary > 1000;

Warning: Be very careful if you decide to use natural joins inside applications or scripts, since it can cause you problems in the future if the joined tables are subject to modifications. Your current joins would be broken if the name of one of the joined columns change, and new joins can be added unintentionally if you rename or add new columns.