7.1 ANSI/ISO SQL Equijoins & Natural Joins

7.1 ANSI/ISO SQL Equijoins & Natural Joins

In relational database management systems, data is normalized across multiple specialized tables to eliminate redundancy and maintain referential integrity. For example, employee demographic data resides in an EMPLOYEES table, whereas department metadata resides in a DEPARTMENTS table. To generate comprehensive reports, SQL queries must reconstruct these relationships by combining rows from multiple tables using joins.

Oracle SQL supports two primary syntax styles for joins:

1. ANSI/ISO SQL:1999 Standard Syntax: Uses dedicated keywords (NATURAL JOIN, JOIN ... USING, JOIN ... ON, CROSS JOIN, LEFT/RIGHT/FULL OUTER JOIN). This is the modern, highly readable industry standard.
2. Legacy Oracle-Specific Syntax (Oracle 8i and earlier): Lists all tables in the FROM clause separated by commas and defines join conditions in the WHERE clause.

The Oracle Database SQL Certified Associate (1Z0-071) exam tests ANSI join syntax extensively, with special focus on equijoins, syntax restrictions, column qualification rules, and specific Oracle compilation errors (ORA-25155 and ORA-25154).

---

Equijoin Fundamentals

An equijoin (also known as an equality join or simple inner join) is a join operation where the join condition is based strictly on an equality comparison operator (=) between columns from participating tables. Typically, an equijoin matches a foreign key column in one table with a primary key or unique key column in another table.

+-----------------------------------------------------------------------------------+
|                             EQUIJOIN DATA MATCHING                                |
|                                                                                   |
|  EMPLOYEES TABLE                                 DEPARTMENTS TABLE                |
|  +--------+-----------+---------------+          +---------------+--------------+ |
|  | EMP_ID | LAST_NAME | DEPARTMENT_ID |          | DEPARTMENT_ID | DEPT_NAME    | |
|  +--------+-----------+---------------+          +---------------+--------------+ |
|  | 100    | King      | 90            |--------->| 10            | Admin        | |
|  | 101    | Kochhar   | 90            |-----+    | 20            | Marketing    | |
|  | 102    | De Haan   | 90            |---+ |    | 90            | Executive    | |
|  | 103    | Hunold    | 60            |-+ | +--->+---------------+--------------+ |
|  +--------+-----------+---------------+ | | |                                     |
|                                         | | +---> Match: King, Kochhar, De Haan   |
|                                         | |       joined with Executive (90)      |
|                                         | +-----> Match: Hunold joined with       |
|                                         +-------> IT (60)                         |
+-----------------------------------------------------------------------------------+

An inner equijoin returns only the rows that satisfy the join condition. If an employee has a NULL department ID, or if a department currently has zero assigned employees, those records are omitted from an inner equijoin result set.

---

The NATURAL JOIN Clause

The NATURAL JOIN clause joins two tables based on all columns that share identical names and compatible datatypes in both tables. Oracle automatically inspects the data dictionary, identifies all identically named columns, and constructs an equality condition (=) for each matching column pair.

Syntax

SELECT column_list
FROM table1
NATURAL JOIN table2;

Example

SELECT employee_id, last_name, department_name
FROM employees
NATURAL JOIN departments;

In the Oracle standard HR schema, the EMPLOYEES and DEPARTMENTS tables share two identically named columns: DEPARTMENT_ID and MANAGER_ID. Therefore, the above NATURAL JOIN is automatically interpreted by Oracle as:

-- Logical equivalent generated by Oracle:
SELECT employee_id, last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND e.manager_id = d.manager_id;

Critical 1Z0-071 Rule: Qualifier Prohibition (ORA-25155)

The NATURAL JOIN Qualifier Rule: Columns that are shared between the two tables and used in the natural join MUST NOT have a table qualifier or table alias in any clause of the query (SELECT, WHERE, ORDER BY, etc.).

Because Oracle automatically resolves the shared column across both tables, qualifying it with a table name or alias introduces ambiguity into the standard and results in a compilation error:

-- INVALID: Qualifying shared column DEPARTMENT_ID with table alias 'e'
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e
NATURAL JOIN departments d;
-- FAILS: ORA-25155: column used in NATURAL join cannot have qualifier

-- CORRECTED: The shared column DEPARTMENT_ID must appear without qualifier
SELECT e.employee_id, e.last_name, department_id, d.department_name
FROM employees e
NATURAL JOIN departments d;

Datatype Compatibility and the Unintended Match Pitfall

1. Datatype Mismatch (ORA-01722 / ORA-00932): If two columns share the same name but have completely incompatible datatypes (e.g., VARCHAR2 in one table and NUMBER in the other), Oracle raises a datatype mismatch error upon execution.
2. The Multiple Column Matching Pitfall: Because NATURAL JOIN joins on all common column names, having multiple shared columns often yields unexpected results. In the HR schema, joining EMPLOYEES and DEPARTMENTS via NATURAL JOIN matches both department_id AND manager_id. This returns only the narrow subset of employees whose direct manager also happens to be their department's own manager — far fewer rows than the 106 that an equijoin on DEPARTMENT_ID alone returns.

---

The JOIN ... USING Clause

To overcome the unintended multi-column matching of NATURAL JOIN, ANSI SQL provides the JOIN ... USING clause. It allows developers to explicitly specify which shared column(s) should be used for the equijoin.

Syntax

SELECT column_list
FROM table1
JOIN table2 USING (column_name [, column_name2]);

Example

SELECT employee_id, last_name, department_id, department_name
FROM employees
JOIN departments USING (department_id);

In this query, Oracle joins EMPLOYEES and DEPARTMENTS only on DEPARTMENT_ID, completely ignoring the shared MANAGER_ID column.

Critical 1Z0-071 Rule: Qualifier Prohibition (ORA-25154)

The USING Clause Qualifier Rule: Any column listed in the USING clause MUST NOT be qualified with a table name or table alias anywhere in the SQL statement (SELECT, WHERE, ORDER BY, GROUP BY, or HAVING).

-- INVALID: Qualifying DEPARTMENT_ID with alias 'd' or 'e'
SELECT e.last_name, d.department_id, d.department_name
FROM employees e
JOIN departments d USING (department_id);
-- FAILS: ORA-25154: column part of USING clause cannot have qualifier

-- CORRECTED: department_id must be completely unaliased
SELECT e.last_name, department_id, d.department_name
FROM employees e
JOIN departments d USING (department_id)
WHERE department_id IN (10, 20, 30)
ORDER BY department_id;

Multi-Column USING Joins

You can match multiple columns explicitly by listing them inside the parentheses separated by commas:

SELECT last_name, department_id, manager_id, department_name
FROM employees
JOIN departments USING (department_id, manager_id);

---

The JOIN ... ON Clause

The JOIN ... ON clause is the most powerful and versatile ANSI join construct. It allows you to specify explicit boolean join conditions, support columns with different names, apply non-equi predicates, and use table qualifiers freely.

Syntax

SELECT column_list
FROM table1 alias1
JOIN table2 alias2 ON (alias1.column_a = alias2.column_b);

Key Characteristics of the ON Clause

1. Table Qualifiers Allowed and Required for Ambiguity: Unlike NATURAL JOIN and USING, shared columns in the SELECT or WHERE clauses must be qualified with their table name or table alias if the column name exists in multiple joined tables (to prevent ORA-00918: column ambiguously defined).
2. Joining Columns with Different Names: If two tables represent a foreign-primary relationship using different column names (e.g., e.dept_no = d.department_id), the ON clause handles it seamlessly.
3. Multiple and Complex Conditions: The ON clause supports compound boolean expressions (AND, OR), scalar functions, and literal filters.

SELECT 
    e.employee_id,
    e.last_name,
    e.department_id,
    d.department_name,
    d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.salary > 5000
ORDER BY e.employee_id;

Filtering in ON vs. Filtering in WHERE (Inner Joins)

In an inner join, filtering conditions placed in the ON clause produce the exact same final result set as filtering conditions placed in the WHERE clause:

-- Condition inside ON clause (Valid)
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id AND e.department_id = 90);

-- Condition inside WHERE clause (Standard & Recommended for readability)
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.department_id = 90;

---

Three-Way and Multi-Table Joins

Real-world reporting frequently requires joining three, four, or more tables together in a single query. With ANSI SQL, you chain multiple JOIN clauses sequentially.

+-----------------------------------------------------------------------------------+
|                           4-TABLE JOIN ARCHITECTURE                               |
|                                                                                   |
|  [EMPLOYEES]  --JOIN ON (dept_id)--> [DEPARTMENTS]                                |
|                                            |                                      |
|                                     JOIN ON (loc_id)                              |
|                                            v                                      |
|  [COUNTRIES] <--JOIN ON (country_id)-- [LOCATIONS]                                |
+-----------------------------------------------------------------------------------+

Step-by-Step Multi-Table Join Walkthrough

To list each employee's name, department name, street address, and country name, we join four tables: EMPLOYEES, DEPARTMENTS, LOCATIONS, and COUNTRIES:

SELECT 
    e.employee_id,
    e.last_name,
    d.department_name,
    l.street_address,
    l.city,
    c.country_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l   ON (d.location_id = l.location_id)
JOIN countries c   ON (l.country_id = c.country_id)
WHERE c.country_id IN ('US', 'UK', 'CA')
ORDER BY e.last_name;

Combining Different ANSI Join Styles

You can even mix JOIN ... ON and JOIN ... USING across different join steps within the same query:

SELECT 
    e.last_name,
    department_id,
    d.department_name,
    l.city
FROM employees e
JOIN departments d USING (department_id)            -- Uses USING: department_id unaliased
JOIN locations l   ON (d.location_id = l.location_id); -- Uses ON: location_id aliased

---

Syntax Comparison & Error Reference Table