Stepwise-Lab

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;
  1. It fails: the USING column belongs to neither table, so it cannot be qualified anywhere (ORA-25154).
  2. It runs: the no-qualifier rule covers only the SELECT list, so ORDER BY may name either table's column.
  3. It fails: department_id is in both tables, so the bare name in the SELECT list is ambiguous (ORA-00918).
  4. 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;
  1. It fails: department_id is in both tables, and with ON the name must say which one (ORA-00918).
  2. It fails: a column used in ON cannot appear again in WHERE (ORA-00904).
  3. It runs and returns the four IT employees: Oracle takes employees.department_id, the first table's column.
  4. 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(+);
  1. It fails: (+) can mark only one side of a condition (ORA-01468).
  2. It returns every customer and every order, like FULL OUTER JOIN.
  3. It returns only the matched rows, like an inner join.
  4. 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(+);
  1. ORA-25156: old style outer join (+) cannot be used with ANSI joins
  2. It returns every employee with their department and grade, as two outer joins
  3. ORA-01468: a predicate may reference only one outer-joined table
  4. 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.)