Oracle SQL JOINs: What the 1Z0-071 Exam Actually Tests

1Z0-071 Exam Prep Chapter 7 Volume 2

JOINs are one of the largest topic areas on the 1Z0-071 exam. The questions go beyond just writing a basic JOIN — they test whether you know exactly which rows each join type returns, what happens when the join condition finds no match, and how Oracle handles the older comma-join syntax alongside the ANSI syntax.

This post covers every JOIN type tested on the exam with working examples and the specific traps the exam sets for each one.

In this post

1. INNER JOIN — matched rows only
2. LEFT OUTER JOIN — all rows from the left table
3. RIGHT OUTER JOIN — all rows from the right table
4. FULL OUTER JOIN — all rows from both tables
5. CROSS JOIN — every combination
6. NATURAL JOIN and USING — the shortcut joins
7. Oracle legacy syntax (+) — what you still need to know
8. Joining more than two tables
9. Quick reference
10. Practice questions

01 — INNER JOIN — matched rows only

INNER JOIN returns only rows where the join condition is satisfied in both tables. If a row in either table has no matching row in the other, it does not appear in the result.

-- ANSI syntax (preferred, and what the exam expects)
SELECT e.last_name, d.department_name
FROM   employees e
JOIN   departments d
  ON   e.department_id = d.department_id;

-- The keyword INNER is optional — JOIN alone means INNER JOIN
SELECT e.last_name, d.department_name
FROM   employees e
INNER JOIN departments d
  ON   e.department_id = d.department_id;

Row count trap: If the EMPLOYEES table has 107 rows but only 106 have a department_id that matches a row in DEPARTMENTS, the INNER JOIN returns 106 rows — not 107. The unmatched row is silently dropped. The exam tests this exact scenario.

02 — LEFT OUTER JOIN — all rows from the left table

LEFT OUTER JOIN returns every row from the left (first) table, plus matching rows from the right table. Where there is no match on the right side, the right-side columns come back as NULL.

-- All employees returned, including those with no department
SELECT e.last_name, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
  ON   e.department_id = d.department_id;

-- Employees with no department_id get NULL in department_name
-- OUTER keyword is optional — LEFT JOIN means the same thing

How to read it: The LEFT table is the one written before the JOIN keyword. All its rows appear in the result regardless of whether a match exists on the right side.

03 — RIGHT OUTER JOIN — all rows from the right table

RIGHT OUTER JOIN is the mirror of LEFT. All rows from the right (second) table are returned. Where there is no matching row on the left, the left-side columns come back as NULL.

-- All departments returned, including those with no employees
SELECT e.last_name, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
  ON   e.department_id = d.department_id;

-- Departments with no employees get NULL in last_name
-- The same result could be written as a LEFT JOIN with tables swapped

Exam note: Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. The exam may show both forms and ask whether they produce the same result — they do.

04 — FULL OUTER JOIN — all rows from both tables

FULL OUTER JOIN returns all rows from both tables. Where a match exists, the columns from both sides are populated. Where there is no match on either side, the columns from the non-matching table come back as NULL.

-- Every employee and every department appears in the result
SELECT e.last_name, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
  ON   e.department_id = d.department_id;

-- Employees with no department: department_name is NULL
-- Departments with no employees: last_name is NULL
-- Matched rows: both columns populated

Join type	Unmatched left rows	Unmatched right rows
INNER JOIN	Excluded	Excluded
LEFT OUTER JOIN	Included (NULLs on right)	Excluded
RIGHT OUTER JOIN	Excluded	Included (NULLs on left)
FULL OUTER JOIN	Included (NULLs on right)	Included (NULLs on left)

05 — CROSS JOIN — every combination

CROSS JOIN produces a Cartesian product — every row from the first table combined with every row from the second. There is no join condition. If the first table has 10 rows and the second has 5, the result has 50 rows.

-- Returns 10 * 5 = 50 rows
SELECT e.last_name, d.department_name
FROM   employees e
CROSS JOIN departments d;

-- Old syntax that produces the same result (accidental Cartesian product)
SELECT e.last_name, d.department_name
FROM   employees e, departments d;
-- No WHERE clause = Cartesian product, same as CROSS JOIN

Exam trap: The exam will show the old comma syntax without a WHERE clause and ask what the result is. The answer is always a Cartesian product — every row from the first table paired with every row from the second.

06 — NATURAL JOIN and USING — the shortcut joins

NATURAL JOIN automatically joins on all columns that share the same name in both tables. No ON clause needed — Oracle figures out the join columns itself.

-- Oracle finds all matching column names and joins on all of them
SELECT last_name, department_name
FROM   employees
NATURAL JOIN departments;

-- USING gives you control over which shared column to join on
SELECT last_name, department_name
FROM   employees
JOIN   departments USING (department_id);

Critical exam rule — NATURAL JOIN and USING columns: When you use NATURAL JOIN or USING, the join column must NOT be prefixed with a table alias or table name. Writing e.department_id in a query that uses NATURAL JOIN or USING (department_id) raises an error. The exam tests this directly.

-- WRONG — raises ORA-25154 with USING
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
JOIN   departments d USING (department_id)
WHERE  e.department_id = 50;
-- Error: column part of USING clause cannot have qualifier

-- CORRECT — no alias on the USING column
SELECT e.last_name, department_id, d.department_name
FROM   employees e
JOIN   departments d USING (department_id)
WHERE  department_id = 50;

07 — Oracle legacy syntax (+) — what you still need to know

Before ANSI JOIN syntax, Oracle used the (+) operator to indicate the optional side of an outer join. This syntax still appears on the 1Z0-071 exam, so you need to be able to read it.

-- Legacy LEFT OUTER JOIN
SELECT e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id(+);
-- (+) is on the RIGHT side — so the LEFT table (employees) drives the result
-- Equivalent to: FROM employees e LEFT OUTER JOIN departments d ON ...

-- Legacy RIGHT OUTER JOIN
SELECT e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id(+) = d.department_id;
-- (+) is on the LEFT side — so the RIGHT table (departments) drives
-- Equivalent to: FROM employees e RIGHT OUTER JOIN departments d ON ...

How to remember it: The (+) goes on the side that can be NULL — the optional side, the side that may have no match. The table without the (+) is the one whose rows all appear in the result.

Legacy syntax limitations: The (+) operator cannot replicate FULL OUTER JOIN. You also cannot use (+) on both sides of a condition. For these cases, ANSI syntax is the only option.

08 — Joining more than two tables

The exam will give you queries joining three or more tables and ask whether the syntax is correct or what the result will be. The rule is simple: add one JOIN ... ON block per additional table.

-- Three-table join: employees, departments, locations
SELECT e.last_name,
       d.department_name,
       l.city
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
JOIN   locations l   ON d.location_id   = l.location_id;

-- Self join — joining a table to itself
-- Classic example: employee and their manager (both in EMPLOYEES)
SELECT e.last_name   AS employee,
       m.last_name   AS manager
FROM   employees e
JOIN   employees m ON e.manager_id = m.employee_id;

Self join rule: When joining a table to itself, you must use two different aliases — one for each "copy" of the table. Without aliases, Oracle cannot distinguish which instance of the table you mean.

09 — Quick reference

Join type	What it returns	Key exam point
INNER JOIN	Matched rows only	Unmatched rows are silently dropped
LEFT OUTER JOIN	All left rows + matched right rows	No match on right = NULLs in right columns
RIGHT OUTER JOIN	All right rows + matched left rows	Equivalent to LEFT JOIN with tables swapped
FULL OUTER JOIN	All rows from both tables	NULLs on whichever side has no match
CROSS JOIN	Cartesian product (n × m rows)	No ON clause — comma syntax without WHERE does the same
NATURAL JOIN	Joins on all same-name columns	Join column cannot have a table alias
JOIN ... USING	Joins on specified same-name column	USING column cannot have a table alias
Legacy (+)	Outer join pre-ANSI	(+) goes on the optional/NULL side; cannot do FULL OUTER

10 — Practice questions

Q1 — The EMPLOYEES table has 107 rows. One employee has a NULL department_id, and one department in the DEPARTMENTS table has no employees. How many rows does this return?

SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

A) 107 B) 108 C) 106 D) 105

Q2 — Which query will raise an error?

A) SELECT e.last_name, department_id FROM employees e JOIN departments d USING (department_id); B) SELECT e.last_name, d.department_id FROM employees e JOIN departments d USING (department_id); C) SELECT last_name, department_name FROM employees NATURAL JOIN departments; D) SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

Q3 — What does the (+) operator indicate in Oracle legacy join syntax?

WHERE e.department_id = d.department_id(+)

A) The departments table drives the result — all department rows are returned B) The employees table drives the result — all employee rows are returned C) Both tables are fully returned — equivalent to FULL OUTER JOIN D) The query will raise an error because (+) is deprecated

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

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Chapter 7 alone has 8 lessons and dozens of practice questions covering every JOIN type, non-equijoins, and self-joins. Instant PDF download.

Get the full guide — $25 →

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