Oracle SQL Non-Equijoins, Self-Joins and the Traps That Cost the Most Marks — 1Z0-071 Exam Guide

1Z0-071 Exam Prep Chapter 7 Volume 2

Post 02 covered the main JOIN types. This post goes deeper into two JOIN patterns that appear on the exam in their own right — non-equijoins and self-joins — then closes with a focused look at the cross-topic traps that consistently cost candidates marks: the ones where you know the concept but the exam wording makes you second-guess yourself.

These traps are worth reviewing before exam day because they are not about gaps in knowledge — they are about precision under pressure.

In this post

1. Non-equijoins — joining on a range rather than equality
2. Non-equijoin with BETWEEN
3. Self-joins — joining a table to itself
4. Self-join for hierarchical data
5. ANSI JOIN vs Oracle legacy syntax — side by side
6. Cross-topic trap: NULL in comparisons
7. Cross-topic trap: WHERE vs HAVING confusion
8. Cross-topic trap: SELECT alias availability
9. Cross-topic trap: DDL and implicit COMMIT
10. Cross-topic trap: constraint type codes
11. Quick reference
12. Practice questions

01 — Non-equijoins — joining on a range rather than equality

Most JOINs use an equality condition — employee's department_id equals department's department_id. A non-equijoin uses any other comparison operator: >, <, >=, <=, BETWEEN, or !=. The join condition matches a value in one table to a range or condition in another.

-- Classic non-equijoin example: salary grades
-- JOB_GRADES table has LOWEST_SAL and HIGHEST_SAL columns
-- We want to find which grade each employee's salary falls into

SELECT e.last_name,
       e.salary,
       j.grade_level
FROM   employees e
JOIN   job_grades j
  ON   e.salary BETWEEN j.lowest_sal AND j.highest_sal;
-- No shared column name — the join matches a value to a range
-- Each employee salary falls into exactly one grade band

-- Same query using legacy Oracle syntax
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

Non-equijoin has no shared column: Unlike an equijoin where both tables have a matching column (like department_id in both EMPLOYEES and DEPARTMENTS), a non-equijoin typically matches a single value from one table against a range defined by two columns in another. NATURAL JOIN and USING cannot be used for non-equijoins — only ON or the legacy WHERE syntax works.

02 — Non-equijoin with BETWEEN

BETWEEN is the most common operator in non-equijoin conditions. The same inclusive boundary rule applies here as in WHERE clauses — both boundary values are included in the match.

-- JOB_GRADES table structure
┌─────────────┬────────────┬─────────────┐
│ GRADE_LEVEL │ LOWEST_SAL │ HIGHEST_SAL │
├─────────────┼────────────┼─────────────┤
│      A      │   1000     │    2999      │
│      B      │   3000     │    5999      │
│      C      │   6000     │    9999      │
│      D      │  10000     │   14999      │
│      E      │  15000     │   24999      │
└─────────────┴────────────┴─────────────┘

-- An employee with salary 5999 matches grade B (BETWEEN 3000 AND 5999)
-- An employee with salary 6000 matches grade C (BETWEEN 6000 AND 9999)
-- An employee with salary 3000 matches grade B (boundary value is included)

-- Non-equijoin with > and < operators
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e
JOIN   job_grades j
  ON   e.salary >= j.lowest_sal
 AND   e.salary <= j.highest_sal;
-- Identical result to the BETWEEN version

Gap risk in grade tables: If the grade table has a gap — HIGHEST_SAL for grade B is 5999 and LOWEST_SAL for grade C is 6001 — a salary of exactly 6000 would match no grade and the employee would be excluded from the result. The exam tests this by asking how many rows appear when the join condition finds no match for some rows.

03 — Self-joins — joining a table to itself

A self-join connects a table to itself to compare rows within the same table. The classic Oracle example is finding an employee's manager — both the employee and the manager are stored in the same EMPLOYEES table.

-- EMPLOYEES table has MANAGER_ID which references EMPLOYEE_ID in the same table
-- To show employee names alongside their manager names:

SELECT e.last_name   AS employee,
       m.last_name   AS manager
FROM   employees e
JOIN   employees m
  ON   e.manager_id = m.employee_id;
-- 'e' is the employee alias — one copy of the table
-- 'm' is the manager alias — another copy of the same table
-- The join links employee.manager_id to the manager's employee_id

-- INNER JOIN version excludes the employee with no manager (CEO)
-- King has NULL manager_id — no match, excluded from INNER JOIN result

-- LEFT OUTER JOIN includes the CEO (NULL manager_id shows NULL for manager)
SELECT e.last_name   AS employee,
       m.last_name   AS manager
FROM   employees e
LEFT OUTER JOIN employees m
  ON   e.manager_id = m.employee_id;
-- Returns all employees including King (manager column shows NULL)

Two aliases are mandatory: A self-join requires two different table aliases — one for each "copy" of the table. Without aliases Oracle cannot distinguish which instance you are referring to in the SELECT list or ON clause. The exam will present self-joins without aliases as a wrong answer option.

04 — Self-join for hierarchical data

Self-joins are used whenever a table has a recursive relationship — a row that references another row in the same table. Common examples in the exam are employee-manager hierarchies and category-subcategory trees.

-- Find all employees managed by King (employee_id = 100)
SELECT e.employee_id,
       e.last_name,
       e.manager_id
FROM   employees e
WHERE  e.manager_id = 100;
-- Direct reports only — one level of hierarchy

-- Show employee and their manager's name side by side
SELECT e.employee_id,
       e.last_name                    AS employee_name,
       e.manager_id,
       m.last_name                    AS manager_name
FROM   employees e
LEFT OUTER JOIN employees m
  ON   e.manager_id = m.employee_id
ORDER BY e.manager_id NULLS FIRST, e.last_name;
-- NULLS FIRST puts King (no manager) at the top

Self-join vs CONNECT BY: Oracle also supports hierarchical queries using CONNECT BY / START WITH — a proprietary extension for walking tree structures. The 1Z0-071 exam focuses on the standard self-join approach, not CONNECT BY. If CONNECT BY appears in an answer option, it is usually not what the exam is testing.

05 — ANSI JOIN vs Oracle legacy syntax — side by side

The exam presents both syntaxes and tests whether you can read either one. This table is worth memorising — it comes up across several question types.

Join type	ANSI syntax	Oracle legacy syntax
INNER JOIN	JOIN t2 ON t1.col = t2.col	FROM t1, t2 WHERE t1.col = t2.col
LEFT OUTER JOIN	LEFT OUTER JOIN t2 ON t1.col = t2.col	WHERE t1.col = t2.col(+)
RIGHT OUTER JOIN	RIGHT OUTER JOIN t2 ON t1.col = t2.col	WHERE t1.col(+) = t2.col
FULL OUTER JOIN	FULL OUTER JOIN t2 ON t1.col = t2.col	Not possible with (+)
CROSS JOIN	CROSS JOIN t2	FROM t1, t2 (no WHERE)
Non-equijoin	JOIN t2 ON t1.val BETWEEN t2.lo AND t2.hi	WHERE t1.val BETWEEN t2.lo AND t2.hi
Self-join	JOIN employees m ON e.mgr_id = m.emp_id	FROM employees e, employees m WHERE e.mgr_id = m.emp_id

06 — Cross-topic trap: NULL in comparisons

NULL is the single most tested concept across the entire exam. It appears in every topic. The traps are always the same but they work because each topic presents them in a slightly different context.

── The three NULL traps that appear in every topic ──────────────

1. Comparison operators always return UNKNOWN with NULL
WHERE commission_pct != 0.25
-- Employees with NULL commission_pct are EXCLUDED silently
-- The != produces UNKNOWN for NULL — UNKNOWN rows are filtered out

2. NOT IN with NULL in the list returns zero rows
WHERE department_id NOT IN (10, 20, NULL)
-- ZERO rows — NULL in NOT IN list makes every comparison UNKNOWN

3. Aggregate functions skip NULL
SELECT AVG(commission_pct) FROM employees
-- Divides by the count of non-null rows — not total rows
-- Result is HIGHER than if NULLs were treated as zero

── NULL in JOIN conditions ───────────────────────────────────────
SELECT * FROM employees e
JOIN   departments d ON e.department_id = d.department_id
-- Employees with NULL department_id are EXCLUDED from INNER JOIN
-- e.department_id = d.department_id is UNKNOWN when dept_id is NULL

07 — Cross-topic trap: WHERE vs HAVING confusion

The WHERE/HAVING distinction is tested across aggregation, subquery, and filtering questions. The trap is usually a query that is almost correct but uses the wrong clause.

-- WRONG: aggregate function in WHERE
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000     -- ORA-00934: group function not allowed here
GROUP BY department_id;

-- CORRECT: aggregate function in HAVING
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary) > 8000;

-- ALLOWED: non-aggregate condition in HAVING (unusual but valid)
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   department_id > 50;   -- valid but WHERE is more efficient here

-- The key rule: WHERE filters ROWS before grouping
--              HAVING filters GROUPS after grouping
-- You CAN use non-aggregate conditions in HAVING — it just runs later

Non-aggregate condition in HAVING is valid but inefficient: A condition like HAVING department_id = 50 works — but it filters after all groups are formed. Putting the same condition in WHERE filters rows before grouping, which is more efficient. The exam tests that you know HAVING accepts non-aggregate conditions, not just aggregate ones.

08 — Cross-topic trap: SELECT alias availability

Column alias availability is tested in questions about GROUP BY, WHERE, HAVING, and ORDER BY. The rule is simple but the exam applies it in confusing ways.

SELECT department_id,
       AVG(salary)    AS avg_sal,
       COUNT(*)       AS headcount
FROM   employees
GROUP BY department_id
HAVING   avg_sal > 8000          -- WRONG: alias not available in HAVING
ORDER BY avg_sal DESC;           -- CORRECT: alias available in ORDER BY

-- Corrected version:
HAVING   AVG(salary) > 8000    -- must repeat the expression
ORDER BY avg_sal DESC;           -- alias works here

Clause	SELECT alias available?	Reason
WHERE	No	Runs before SELECT — alias not yet defined
GROUP BY	No	Runs before SELECT — alias not yet defined
HAVING	No	Runs before SELECT — alias not yet defined
ORDER BY	Yes	Runs after SELECT — alias is available

09 — Cross-topic trap: DDL and implicit COMMIT

The implicit COMMIT from DDL is tested in questions about transaction control, ROLLBACK, and data integrity. Candidates who forget this rule choose the wrong answer about what can be undone.

── Scenario the exam uses ────────────────────────────────────────

Step 1: INSERT some rows (DML — not yet committed)
INSERT INTO departments VALUES (300, 'Alpha', NULL, NULL);
INSERT INTO departments VALUES (310, 'Beta',  NULL, NULL);

Step 2: Run DDL (implicitly commits both INSERTs)
CREATE TABLE temp_log (id NUMBER);

Step 3: Run ROLLBACK
ROLLBACK;
-- The two INSERTs are ALREADY committed — ROLLBACK has nothing to undo
-- Departments 300 and 310 remain in the database

── DDL statements that cause implicit COMMIT ────────────────────
-- CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE
-- DROP TABLE, DROP VIEW, DROP INDEX
-- ALTER TABLE, TRUNCATE TABLE
-- RENAME, COMMENT
-- Any DDL statement commits all preceding uncommitted DML

10 — Cross-topic trap: constraint type codes

Data dictionary questions often require knowing the one-letter type codes stored in USER_CONSTRAINTS. These are short enough to memorise but easy to confuse under pressure.

Code	Constraint type	Exam trap
P	PRIMARY KEY	Easy to remember
U	UNIQUE	Easy to remember
R	FOREIGN KEY (Referential)	Called R, not F — candidates guess F
C	CHECK (and NOT NULL)	NOT NULL is stored as C, not as its own type

-- Query that reveals all four types on the EMPLOYEES table
SELECT constraint_name,
       constraint_type,
       search_condition,
       status
FROM   user_constraints
WHERE  table_name = 'EMPLOYEES'
ORDER BY constraint_type;

-- Sample result:
-- EMP_PK          P    (null)                  ENABLED
-- EMP_EMAIL_UK     U    (null)                  ENABLED
-- EMP_DEPT_FK      R    (null)                  ENABLED
-- EMP_JOB_FK       R    (null)                  ENABLED
-- EMP_SALARY_MIN   C    salary > 0              ENABLED
-- SYS_C001234      C    "LAST_NAME" IS NOT NULL  ENABLED  ← NOT NULL stored as C

Foreign key type is R, not F: The most common mistake on data dictionary questions about constraints. The FOREIGN KEY constraint type is stored as 'R' (for Referential integrity), not 'F'. The exam uses this in questions that ask you to identify constraint types from a USER_CONSTRAINTS query result.

11 — Quick reference

Rule	Detail
Non-equijoin	Uses >, <, >=, <=, BETWEEN, != in ON/WHERE — not equality
NATURAL JOIN / USING for non-equijoin	Not valid — only ON or legacy WHERE works
Self-join requires two aliases	Cannot join a table to itself without distinct aliases
Self-join with INNER JOIN	Excludes top-level row (e.g. CEO with NULL manager_id)
Self-join with LEFT OUTER JOIN	Includes all rows including the one with NULL FK
Legacy (+) cannot do FULL OUTER JOIN	Must use ANSI FULL OUTER JOIN syntax
NULL in comparison → UNKNOWN → row excluded	Applies in WHERE, JOIN ON, and HAVING
NOT IN with NULL → zero rows	The most common NULL trap in the exam
Aggregate in WHERE → ORA-00934	Use HAVING for aggregate conditions
SELECT alias in ORDER BY → valid	Not valid in WHERE, GROUP BY, or HAVING
Any DDL → implicit COMMIT	All pending DML committed before the DDL runs
FK constraint type = 'R'	Not 'F' — stored as Referential in USER_CONSTRAINTS
NOT NULL constraint type = 'C'	Stored as a CHECK constraint — no separate type

12 — Practice questions

Q1 — The EMPLOYEES table has 107 rows. 1 employee (the CEO) has NULL in MANAGER_ID. A self-join is written as an INNER JOIN on e.manager_id = m.employee_id. How many rows does the result contain?

A) 107 — all employees appear B) 106 — the CEO with NULL manager_id has no match and is excluded C) 108 — the CEO appears twice because they manage themselves D) 0 — self-joins always return zero rows

Q2 — A developer queries USER_CONSTRAINTS and finds a row where CONSTRAINT_TYPE = 'R'. What type of constraint is this?

A) A REFERENCES constraint — equivalent to a UNIQUE constraint B) A FOREIGN KEY constraint — R stands for Referential integrity C) A CHECK constraint where the condition uses a REFERENCES clause D) A ROW-level constraint applied to the entire row

Q3 — A developer runs these statements in order:

INSERT INTO emp_log VALUES (1, 'Start');
SAVEPOINT sp1;
INSERT INTO emp_log VALUES (2, 'Middle');
DROP TABLE temp_work;
ROLLBACK TO sp1;

Which rows are in EMP_LOG after all statements complete?

A) Both rows (1 and 2) — ROLLBACK TO sp1 only undoes to the savepoint B) Row 1 only — ROLLBACK TO sp1 undoes row 2 but not row 1 C) Both rows (1 and 2) — DROP TABLE committed everything before it ran D) No rows — ROLLBACK TO sp1 undoes all DML back to the beginning

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Chapter 7 covers non-equijoins, self-joins, and every join type in full — with practice sets built around the specific traps the exam uses. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.