7.2 Non-Equijoins, Self Joins, and Cartesian Products

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

1. 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.
2. 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.).
3. 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$ rows and Table B contains $N$ rows, their Cartesian product produces:

$$\text{Total Rows} = M \times N$$

For three tables with $M, N, P$ rows:

$$\text{Total Rows} = M \times N \times 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 \times 23 = \mathbf{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