7.1 ANSI/ISO SQL Equijoins & Natural Joins

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

7.1 ANSI/ISO SQL Equijoins & Natural Joins

Key Takeaways Explain This Topic AI

Equijoins combine rows from two or more tables based on an equality condition between corresponding columns, typically linking primary and foreign keys.

The NATURAL JOIN clause automatically joins tables by matching all columns with identical names and compatible datatypes across both tables.

Crucial 1Z0-071 Rule: Columns referenced in a NATURAL JOIN or listed in a USING clause must NEVER be qualified with a table name or table alias; doing so raises ORA-25155 or ORA-25154 respectively.

The JOIN ... USING (col, ...) clause provides explicit control by matching only the specified shared columns, resolving the unintended multi-column matching pitfall of NATURAL JOIN.

The JOIN ... ON clause offers maximum flexibility, permitting explicit boolean predicates, table-qualified column references, joins on mismatched column names, and multi-table chaining.

Last updated: August 2026

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:

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.

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

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.

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

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 ).

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.

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

Join Clause How Join Columns Are Determined Column Qualifiers in SELECT/WHERE Common Error Code

NATURAL JOIN Automatically matches ALL columns with identical names and types. Strictly Forbidden on join columns. ORA-25155 : column used in NATURAL join cannot have qualifier

JOIN ... USING Explicitly matches named columns inside (col1, col2) . Strictly Forbidden on listed columns. ORA-25154 : column part of USING clause cannot have qualifier

JOIN ... ON Explicitly specified via boolean condition (t1.col = t2.col) . Allowed & Required for ambiguous columns. ORA-00918 : column ambiguously defined

Legacy Comma Defined in WHERE clause ( WHERE t1.col = t2.col ). Allowed & Required for ambiguous columns. ORA-00918 : column ambiguously defined