Skip to content

Oracle LEFT & RIGHT JOIN 🌓

Prerequisites: INNER JOIN

Mentor's Note: INNER JOIN is exclusive (both must match). LEFT JOIN is inclusive; it says, "Give me everything from the left list, and fill in the blanks with NULL if there's no match on the right." 💡

🌟 The Scenario: The Customer Audit 🛒

  • Table A (Customers): All registered users.
  • Table B (Orders): All purchases made.
  • Goal: Find customers who have never placed an order.
  • Action: Use a LEFT JOIN. If the order column is NULL, they haven't bought anything!

💻 1. LEFT OUTER JOIN (Standard)

Returns all rows from the Left table.

-- Scenario: List all departments and their employees
SELECT D.department_name, E.first_name
FROM departments D
LEFT JOIN employees E ON D.department_id = E.department_id;

💻 2. RIGHT OUTER JOIN

Returns all rows from the Right table. (Architect's Note: Most devs just use LEFT JOIN and swap table order for readability).

SELECT E.first_name, D.department_name
FROM employees E
RIGHT JOIN departments D ON E.department_id = D.department_id;

🏗️ Architect's Note: The Legacy (+) Syntax 🛡️

You will see old Oracle code using the (+) operator. 

-- ⚠️ LEGACY (Do not use in new projects!)
SELECT D.department_name, E.first_name
FROM departments D, employees E
WHERE D.department_id = E.department_id(+);
- Architect's Advice: Always use the ANSI-Standard (LEFT JOIN) syntax. It is easier to read, portable to other databases, and less error-prone!

📈 Learning Path