ANSI Joins

NATURAL Joins

A natural join, as its name implies, can be invoked when two or more tables share exactly the same columns needed for a successful equijoin. For example, these queries will return all Region and Country information for all countries whose name that contains the string "united":
Example: NATURAL Join

Traditional Syntax :
SELECT
r.region_id,
r.region_name,
c.country_id,
c.country_name
FROM
countries c,
regions r
WHERE c.region_id = r.region_id
AND LOWER(c.country_name) LIKE '%united%';

ANSI SYNTAX
SELECT
region_id,
r.region_name,
c.country_id,
c.country_name
FROM countries c
NATURAL JOIN regions r
WHERE LOWER(c.country_name) LIKE '%united%';
Note that even though the table names are prefixed in both examples, REGION_ID cannot use the prefix; Oracle chooses the appropriate column in the naturally-joined table from which to gather data. If you get an ORA-25155: column used in NATURAL join cannot have qualifier error message, check over the columns named in the query – you may have inadvertently broken this rule.

JOIN ... USING. When you need to join tables that share more than one column naturally, the JOIN ... USING syntax needs to be used. A NATURAL join between the Employees and Departments tables, for example, could yield unexpected results because the tables share both the DEPARTMENT_ID and MANAGER_ID columns, so the JOIN...USING syntax can be used to alleviate this issue. These queries will display Department and Employee information for all employees hired after December 31, 1999:

Example: JOIN...USING
Traditional Syntax
SELECT
d.department_id ' - ' d.department_name "Department",
e.employee_id,
e.last_name ', ' e.first_name "Name",
e.hire_date
FROM
employees e,
departments d
WHERE e.department_id = d.department_id
AND e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');


ANSI SYNTAX
SELECT
department_id ' - ' d.department_name "Department",
employee_id,
e.last_name ', ' e.first_name "Name",
e.hire_date
FROM employees e
JOIN departments d USING (department_id)
WHERE e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');
Again, note that as with the NATURAL JOIN syntax, it's improper to use the table prefix for the columns specified in the JOIN...USING statement (in this case, DEPARTMENT_ID).


JOIN ... ON. When you need to describe exactly how two or more tables should be joined together, the ANSI JOIN...ON syntax is ideal. Notice that these queries are joining three tables together to return employee, job, and job history information for all employees hired after December 31, 1999:
Example: JOIN...ON

Traditional Syntax
SELECT
employee_id,
e.last_name ', ' e.first_name "Name",
job_id,
j.job_title,
jh.start_date,
jh.end_date
FROM
employees e,
jobs j,
job_history jh
WHERE e.job_id = j.job_id
AND e.job_id = jh.job_id
AND e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');

ANSI SYNTAX
SELECT
employee_id,
e.last_name ', ' e.first_name "Name",
job_id,
j.job_title,
jh.start_date,
jh.end_date
FROM employees e
JOIN jobs j ON (j.job_id = e.job_id)
JOIN job_history jh ON (jh.job_id = j.job_id)
WHERE e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');

Outer Joins

Outer joins are usually needed when all rows of one side of the join equation must be retrieved regardless of how many matches are found between the other side of the equation.
A left outer join is constructed whenever all rows on the left side of the join equation need to be returned regardless of whether or not any rows exist on the right side of the join. The traditional syntax uses a plus sign in parentheses – (+) – to indicate which side of the join may not contain any corresponding rows.
In the following example, the query needs to return a total count of all Employees for all Departments regardless of whether some Departments have no employees:
Example: LEFT OUTER Join

Traditional Syntax
SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) "Employees"
FROM
departments d,
employees e
WHERE d.department_id = e.department_id (+)
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id, d.department_name;

ANSI SYNTAX
SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) "Employees"
FROM
departments d LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id, d.department_name;

Likewise, a right outer join is invoked whenever all rows on the right side of the join equation need to be returned regardless of whether or not any rows exist on the left side of the join. Here are examples satisfying the opposite of the prior requirement: Return a count of total Employees in each Department regardless of whether some Employees have been assigned a Department that does not yet exist:
Example: RIGHT OUTER Join

Traditional Syntax

SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) "Employees"
FROM
departments d,
employees e
WHERE d.department_id (+) = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id, d.department_name;

ANSI SYNTAX

SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) "Employees"
FROM
departments d RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id, d.department_name;


Finally, there is the full outer join. As its name implies, a full outer join returns results from both sides of the join equation. The only way to perform a full outer join in traditional syntax is via the UNION or UNION ALL operators. However, the ANSI syntax provides the FULL OUTER JOIN to accomplish this instead. As you can see, the FULL OUTER JOIN syntax results in a much more compact query:
Example: FULL OUTER Join

Traditional Syntax
SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) "Employees"
FROM
departments d,
employees e
WHERE d.department_id = e.department_id (+)
GROUP BY d.department_id, d.department_name
UNION
SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) "Employees"
FROM
departments d,
employees e
WHERE d.department_id (+) = e.department_id
GROUP BY d.department_id, d.department_name;

ANSI SYNTAX
SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) "Employees"
FROM
employees e FULL OUTER JOIN departments d
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id, d.department_name;
 


Self-Joins

The recursive join that results when a set of data is joined back to itself is also known as a self-join. This is typically encountered when a parent-child relationship in a hierarchy needs to be traversed. When using the ANSI syntax to accomplish this, only the JOIN...ON syntax will work, and the keyword INNER can be used for clarity. For example, these queries will return a list of Employees hired after December 31, 1999 and their current Managers:
Example: INNER JOIN


Traditional Syntax
SELECT
m.last_name ', ' m.first_name "Manager",
e.last_name ', ' e.first_name "Name",
e.hire_date
FROM
employees e,
employees m
WHERE e.manager_id = m.employee_id
AND e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');

ANSI SYNTAX
SELECT
m.last_name ', ' m.first_name "Manager",
e.last_name ', ' e.first_name "Name",
e.hire_date
FROM employees e
INNER JOIN employees m
ON (e.manager_id = m.employee_id)
WHERE e.hire_date > TO_DATE('12/31/1999', 'mm/dd/yyyy');


Cartesian Products

Though I must admit I have spent a lot of time trying to avoid them, Cartesian products do happen from time to time, usually as the result of an equijoin condition that has been missed in a query using traditional syntax. However, one of the advantages of the ANSI join syntax is that a specific keyword, CROSS JOIN, is required to create a Cartesian product, as shown in these next examples:
Example: CROSS JOIN

Traditional Syntax
SELECT
d.department_name,
l.city,
l.country_id
FROM
departments d,
locations l
WHERE l.country_id = 'JP';

ANSI SYNTAX
SELECT
d.department_name,
l.city,
l.country_id
FROM departments d
CROSS JOIN locations l
WHERE l.country_id = 'JP';

Comment