Joins: 1Z0-171 practice questions
Questions in the style of exam 1Z0-171 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- Various types of joins
- Self-joins
- Non-equijoins
- OUTER joins
- Cartesian products
Practice questions
1. What happens when this query runs?
SELECT department_id, e.last_name
FROM employees e
JOIN departments d USING (department_id)
ORDER BY d.department_id;- It fails: the USING column belongs to neither table, so it cannot be qualified anywhere (ORA-25154).
- It runs: the no-qualifier rule covers only the SELECT list, so ORDER BY may name either table's column.
- It fails: department_id is in both tables, so the bare name in the SELECT list is ambiguous (ORA-00918).
- It fails: ORDER BY may only use columns of the first table in FROM (ORA-00904).
Show the answer
Answer: A
A column named in USING becomes a single join column that belongs to neither table, so qualifying it anywhere in the query, ORDER BY included, raises ORA-25154 (the NATURAL JOIN equivalent is ORA-25155). Write ORDER BY department_id. The bare department_id in the SELECT list is the correct form; ORA-00918 is the opposite problem, an unqualified column that exists in both tables of an ON join.
2. What happens when this query runs?
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE department_id = 30;- It fails: department_id is in both tables, and with ON the name must say which one (ORA-00918).
- It fails: a column used in ON cannot appear again in WHERE (ORA-00904).
- It runs and returns the four IT employees: Oracle takes employees.department_id, the first table's column.
- It runs and returns the four IT employees: in this inner join both columns hold the same value, so either one will do.
Show the answer
Answer: A
With ON, both department_id columns stay separate, so a bare department_id is ambiguous anywhere in the query, WHERE included: ORA-00918. The two values are equal in this inner join, but Oracle still needs to be told which column you mean: write e.department_id or d.department_id. A join column may appear in WHERE as often as you like. With USING or NATURAL JOIN the opposite rule applies: the join column must not be qualified.
3. What happens when you try to write a full outer join like this?
SELECT c.customer_name, o.order_id
FROM customers c, orders o
WHERE c.customer_id(+) = o.customer_id(+);- It fails: (+) can mark only one side of a condition (ORA-01468).
- It returns every customer and every order, like FULL OUTER JOIN.
- It returns only the matched rows, like an inner join.
- It returns every customer paired with every order.
Show the answer
Answer: A
(+) may be placed on one side of a predicate only, so the old syntax cannot express a full outer join: ORA-01468. Use FULL OUTER JOIN (or a UNION of a left and a right join).
4. What happens when you run this query?
SELECT e.last_name, d.department_name, g.grade
FROM employees e
LEFT JOIN departments d ON d.department_id = e.department_id,
salary_grades g
WHERE e.salary BETWEEN g.min_salary(+) AND g.max_salary(+);- ORA-25156: old style outer join (+) cannot be used with ANSI joins
- It returns every employee with their department and grade, as two outer joins
- ORA-01468: a predicate may reference only one outer-joined table
- ORA-00933: SQL command not properly ended, because a comma cannot follow an ANSI join
Show the answer
Answer: A
(+) may not appear in the WHERE clause of a query that also uses ANSI JOIN syntax: ORA-25156. A comma after an ANSI join is legal in itself, and ORA-01468 is for (+) on both sides of one comparison; here only the grade side carries it, which would be fine in pure comma syntax. Fix: LEFT JOIN salary_grades g ON e.salary BETWEEN g.min_salary AND g.max_salary. (Oracle 19c accepts (+) inside an ANSI ON clause, an undocumented exception; Oracle 26ai rejects that too with ORA-25156.)