1Z0-149: Displaying Data from Multiple Tables

What this domain tests

This domain tests joining rows from multiple tables using ANSI join syntax and Oracle's legacy outer-join operator.

Core rules

• An inner join returns only matching rows. NATURAL JOIN joins all same-named columns; USING (col) joins one named column and removes the duplicate column from output.
• ON accepts any join condition, including non-equality conditions.
• A self join uses two aliases for the same table to compare rows within it.
• LEFT OUTER JOIN keeps all left rows; RIGHT OUTER JOIN keeps all right rows; FULL OUTER JOIN keeps unmatched rows from both sides.
• CROSS JOIN produces a Cartesian product.
• The legacy (+) operator marks the side that may have missing rows. It cannot be used with ON or USING.
• Qualify columns that exist in more than one table.

Worked example

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

The left join keeps every employee, including employees with no department. For those rows, d.department_name is NULL. If the query added WHERE d.department_id = 10, employees without a department would be removed, effectively turning the outer join into an inner join.

Traps

• Omitting the join condition and producing a Cartesian product.
• Filtering a nullable side in WHERE and accidentally discarding outer-join rows.
• Using USING and then qualifying the joined column with a table alias.
• Assuming NATURAL JOIN uses only intended columns; it uses every common column name.
• Placing (+) on the wrong side of the predicate.