Study guide
7.2 Non-Equijoins, Self Joins, and Cartesian Products
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.2 Non-Equijoins, Self Joins, and Cartesian Products
Key Takeaways Explain This Topic AI
A non-equijoin links tables using relational comparison operators other than equality (=), such as BETWEEN ... AND ..., >=, <=, >, <, or !=.
A common application of non-equijoins is mapping continuous numeric values (like employee salaries) into categorical ranges (such as salary grade levels in a JOB_GRADES table).
A self join joins a table to itself to model recursive or hierarchical parent-child relationships, strictly requiring distinct table aliases to differentiate the instances.
A CROSS JOIN computes the Cartesian product of two tables, generating M x N rows by pairing every row from the first table with every row from the second table.
Accidental Cartesian products occur when join conditions are omitted or invalid in legacy comma syntax, causing massive result sets and severe database performance degradation.
Last updated: August 2026
7.2 Non-Equijoins, Self Joins, and Cartesian Products
While most relational database queries combine tables based on primary key-foreign key equality, relational database systems frequently require more sophisticated join patterns. Oracle SQL provides robust support for non-equijoins (joining on ranges or inequalities), self joins (joining a table to itself to resolve recursive hierarchies), and Cartesian products (generating all possible row permutations with CROSS JOIN ).
Mastering these join varieties is essential for the 1Z0-071 exam, where questions frequently test range predicates, table aliasing requirements, and row multiplication mechanics.
Non-Equijoins
A non-equijoin is any join condition that does not use the equality operator ( = ). Instead, it uses operators such as BETWEEN ... AND ... , >= , <= , > , < , != , or <> .
Real-World Use Case: Salary Grading
A classic non-equijoin scenario involves evaluating an employee's compensation against a grading bracket table. Consider a JOB_GRADES table that defines salary grade bands: JOB_GRADES TABLE:
- +-------------+------------+-------------+
- | GRADE_LEVEL | LOWEST_SAL | HIGHEST_SAL |
- +-------------+------------+-------------+
- | A | 1000 | 2999 |
- | B | 3000 | 5999 |
- | C | 6000 | 9999 |
- | D | 10000 | 14999 |
- | E | 15000 | 24999 |
- | F | 25000 | 40000 |
- +-------------+------------+-------------+
Because the EMPLOYEES table stores an exact SALARY amount (e.g., 6500 ) rather than a GRADE_LEVEL , we cannot use an equality join. Instead, we use a non-equijoin with BETWEEN ... AND ... : -- ANSI SQL Non-Equijoin Syntax
- SELECT
- e.employee_id,
- e.last_name,
- e.salary,
- g.grade_level
- FROM employees e
- JOIN job_grades g ON (e.salary BETWEEN g.lowest_sal AND g.highest_sal)
ORDER BY e.salary DESC; NON-EQUIJOIN RESULT SET (Sample):
- +-------------+-----------+--------+-------------+
- | EMPLOYEE_ID | LAST_NAME | SALARY | GRADE_LEVEL |
- +-------------+-----------+--------+-------------+
- | 100 | King | 24000 | E |
- | 101 | Kochhar | 17000 | E |
- | 102 | De Haan | 17000 | E |
- | 103 | Hunold | 9000 | C |
- | 104 | Ernst | 6000 | C |
- | 107 | Lorentz | 4200 | B |
- +-------------+-----------+--------+-------------+
- Non-Equijoins Using Comparison Operators
Non-equijoins can also be written using inequality comparison operators: -- Equivalent query using relational operators
- SELECT e.last_name, e.salary, g.grade_level
- FROM employees e
- JOIN job_grades g ON (e.salary >= g.lowest_sal AND e.salary <= g.highest_sal);
Exam Trap: Remember that BETWEEN lower_bound AND upper_bound is inclusive . The lower bound must always be listed first. Writing e.salary BETWEEN g.highest_sal AND g.lowest_sal will evaluate to false for all rows and return zero records.
Self Joins (Recursive / Hierarchical Joins)
A self join is a join in which a table is joined with itself. This technique is used when a table contains a recursive relationship where a column in a row references the primary key of another row within the same table.
The Employee-Manager Hierarchy
In the EMPLOYEES table, each employee record has a MANAGER_ID column that contains the EMPLOYEE_ID of their supervisor: +-----------------------------------------------------------------------------------+
- | RECURSIVE EMPLOYEE-MANAGER RELATION |
- | |
- | EMPLOYEES (Worker Copy: 'w') EMPLOYEES (Manager Copy: 'm') |
- | +--------+-----------+------------+ +--------+-----------+ |
- | | EMP_ID | LAST_NAME | MANAGER_ID | | EMP_ID | LAST_NAME | |
- | +--------+-----------+------------+ +--------+-----------+ |
- | | 101 | Kochhar | 100 |----------->| 100 | King | |
- | | 102 | De Haan | 100 |----------->| 100 | King | |
- | | 103 | Hunold | 102 |----------->| 102 | De Haan | |
- | | 104 | Ernst | 103 |----------->| 103 | Hunold | |
- | | 100 | King | NULL | (No match) | ... | ... | |
- | +--------+-----------+------------+ +--------+-----------+ |
- +-----------------------------------------------------------------------------------+
- Syntax and Mandatory Table Aliases
To perform a self join, you must assign distinct table aliases to the two references of the same table so that Oracle can distinguish between the two roles (e.g., worker w and manager m ). SELECT
- w.employee_id AS emp_id,
- w.last_name AS employee_name,
- m.employee_id AS mgr_id,
- m.last_name AS manager_name
- FROM employees w
- JOIN employees m ON (w.manager_id = m.employee_id)
ORDER BY w.employee_id; SELF JOIN OUTPUT (Sample):
- +--------+---------------+--------+--------------+
- | EMP_ID | EMPLOYEE_NAME | MGR_ID | MANAGER_NAME |
- +--------+---------------+--------+--------------+
- | 101 | Kochhar | 100 | King |
- | 102 | De Haan | 100 | King |
- | 103 | Hunold | 102 | De Haan |
- | 104 | Ernst | 103 | Hunold |
- | 105 | Austin | 103 | Hunold |
- +--------+---------------+--------+--------------+
- Key Rules for Self Joins on 1Z0-071
Mandatory Table Aliases: Without distinct aliases Oracle cannot tell the two copies of the table apart, so every shared column reference fails with ORA-00918: column ambiguously defined .
Mandatory Column Qualification: Every column referenced in the SELECT , ON , WHERE , or ORDER BY clause must be prefixed with the appropriate alias ( w. or m. ).
Exclusion of the Root Record: In an inner self join, employee 100 (King) has MANAGER_ID = NULL . Because NULL = 100 evaluates to UNKNOWN , King is excluded from the result set. (To include King, a LEFT OUTER JOIN is required).
Cartesian Products & The CROSS JOIN Clause
A Cartesian product (or Cartesian join ) is formed when every single row of the first table is paired with every single row of the second table.
Row Count Formula
If Table A contains M M M rows and Table B contains N N N rows, their Cartesian product produces: Total Rows = M × N \text{Total Rows} = M \times N Total Rows = M × N
For three tables with M , N , P M, N, P M , N , P rows: Total Rows = M × N × P \text{Total Rows} = M \times N \times P Total Rows = M × N × P +-----------------------------------------------------------------------------------+
- | CARTESIAN PRODUCT (CROSS JOIN) |
- | |
- | TABLE A (3 Rows) TABLE B (2 Rows) RESULT SET (3 x 2 = 6) |
- | +------+ +-------+ +------+-------+ |
- | | ID_A | | VAL_B | | ID_A | VAL_B | |
- | +------+ +-------+ +------+-------+ |
- | | 1 |---+ +-->| X | | 1 | X | |
- | | 2 |-+ | | +>| Y | | 1 | Y | |
- | | 3 | | | | | +-------+ | 2 | X | |
- | +------+ | +-------------|-+ | 2 | Y | |
- | +---------------|-+ | 3 | X | |
- | + | 3 | Y | |
- | +------+-------+ |
- +-----------------------------------------------------------------------------------+
- ANSI CROSS JOIN Syntax
In ANSI SQL:1999, a Cartesian product is explicitly generated using the CROSS JOIN keyword without any ON or USING clause: -- Explicit ANSI CROSS JOIN
- SELECT d.department_name, l.city
- FROM departments d
- CROSS JOIN locations l;
If DEPARTMENTS has 27 rows and LOCATIONS has 23 rows, the query returns 27 × 23 = 621 27 \times 23 = \mathbf{621} 27 × 23 = 621 rows.
Legacy Comma Syntax & Accidental Cartesian Joins
In legacy Oracle syntax, a Cartesian product occurs whenever you list multiple tables in the FROM clause and omit the WHERE join condition (or write an invalid join condition): -- Legacy Comma Syntax producing Cartesian Product
- SELECT e.last_name, d.department_name
- FROM employees e, departments d;
-- 107 employees x 27 departments = 2,889 rows returned!
Intentional vs. Accidental Cartesian Products +-----------------------------------------------------------------------------------+
- | CARTESIAN JOIN EVALUATION |
- +-----------------------------+-----------------------------------------------------+
- | Intentional Use Cases | - Generating test data with millions of permutations|
- | | - Matrix reporting (e.g., all Products x all Months)|
- | | - Creating calendar dimension tables |
- +-----------------------------+-----------------------------------------------------+
- | Accidental Dangers | - Omitted WHERE join condition in legacy queries |
- | | - Massive memory consumption and temp tablespace exhaustion
- | | - Severe database lockups and CPU spikes |
- +-----------------------------+-----------------------------------------------------+
- Comparison Matrix: Join Types & Characteristics
Join Type Defining Mechanism Syntax Keyword / Predicate Output Cardinality
Equijoin Equality matching on shared key values JOIN ... ON (t1.id = t2.id) or USING (id) Matched subset ≤ M × N \le M \times N ≤ M × N
Non-Equijoin Range or inequality matching JOIN ... ON (t1.val BETWEEN t2.low AND t2.high) Matched subset based on range
Self Join Joins table to itself via distinct aliases FROM employees w JOIN employees m ON (...) Hierarchical pairs ≤ \le ≤ row count
Cross Join Unconditional pairing of all rows CROSS JOIN or legacy FROM t1, t2 Exact multiplication: M × N M \times N M × N