Oracle LEFT OUTER JOIN FAQs with Examples

1. What is a LEFT OUTER JOIN in Oracle?

A LEFT OUTER JOIN returns:

  • All rows from the left table.
  • Matching rows from the right table.
  • NULL values for the right table columns when no matching row exists.

It is commonly used when you want to keep every row from the left table, regardless of whether a match exists in the right table.

Syntax

SELECT column_list
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.column_name = t2.column_name;

You can also omit the OUTER keyword:

SELECT column_list
FROM table1 t1
LEFT JOIN table2 t2
ON t1.column_name = t2.column_name;

2. Why do we use a LEFT OUTER JOIN?

A LEFT OUTER JOIN is used to:

  • Retrieve all records from the primary (left) table.
  • Find rows that have no matching records.
  • Generate reports that include unmatched data.
  • Identify missing relationships between tables.

3. How does a LEFT OUTER JOIN work?

Suppose we have:

EMPLOYEES

EMPLOYEE_ID   EMPLOYEE_NAME   DEPARTMENT_ID
101           John            10
102           David           20
103           Scott           40

DEPARTMENTS

DEPARTMENT_ID   DEPARTMENT_NAME
10              Sales
20              HR
30              Finance

Query

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

Output

Employee   Department
John       Sales
David      HR
Scott      NULL

Scott is returned even though department 40 does not exist.

4. What is the syntax of a LEFT OUTER JOIN?

SELECT *
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

Or simply:

SELECT *
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

5. What is the difference between LEFT JOIN and LEFT OUTER JOIN?

There is no difference.

These statements are equivalent:

  • LEFT JOIN
  • LEFT OUTER JOIN

The keyword OUTER is optional.

6. What happens when there is no matching row?

Oracle returns:

  • All columns from the left table.
  • NULL values for columns from the right table.

Example output:

Employee   Department
Scott      NULL

7. Can a LEFT OUTER JOIN use aliases?

Yes.

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

Aliases improve readability.

8. Can a LEFT OUTER JOIN use a WHERE clause?

Yes.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

This returns only matching rows where the department is Sales. Rows with NULL department values are filtered out.

If you want to keep unmatched rows while filtering the joined table, place the condition in the ON clause: 

SELECT e.employee_name,
       d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
AND d.department_name = 'Sales';

9. Can a LEFT OUTER JOIN use GROUP BY?

Yes.

SELECT d.department_name,
       COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name;

This shows departments even if they have no employees.

10. Can a LEFT OUTER JOIN use ORDER BY?

Yes.

SELECT e.employee_name,
       d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
ORDER BY e.employee_name;

11. Can a LEFT OUTER JOIN use multiple conditions?

Yes.

SELECT *
FROM orders o
LEFT JOIN shipments s
ON o.order_id = s.order_id
AND o.customer_id = s.customer_id;

12. Can a LEFT OUTER JOIN use multiple tables?

Yes.

SELECT e.employee_name,
       d.department_name,
       l.city
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id;

13. Can a LEFT OUTER JOIN return duplicate rows?

Yes. If multiple matching rows exist in the right table, each matching combination is returned.

For example, if one department has multiple matching records in the joined table, an employee may appear multiple times.

14. How do you find unmatched rows using a LEFT OUTER JOIN?

Use IS NULL on a column from the right table.

SELECT e.employee_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

This returns employees who are not assigned to a valid department.

15. What is the old Oracle (+) syntax for LEFT OUTER JOIN?

SELECT e.employee_name,
       d.department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id(+);

Equivalent ANSI syntax:

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

ANSI joins are recommended for new development.

16. What are common uses of a LEFT OUTER JOIN?

  • Employees without departments.
  • Customers without orders.
  • Products without sales.
  • Departments without employees.
  • Students without course registrations.
  • Orders without shipments.

17. Real-Time Example – Customers Without Orders

CUSTOMERS

CUSTOMER_ID   CUSTOMER_NAME
1             Alice
2             Bob
3             Charlie

ORDERS

ORDER_ID   CUSTOMER_ID
100        1
101        2

Query

SELECT c.customer_name,
       o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

Output

Customer   Order
Alice      100
Bob        101
Charlie    NULL

Charlie has not placed any orders.

18. What are the advantages of a LEFT OUTER JOIN?

  • Returns all rows from the left table.
  • Identifies missing relationships.
  • Useful for reporting.
  • Supports optional relationships.
  • Easy to understand.

19. What are the disadvantages of a LEFT OUTER JOIN?

  • Can return large result sets.
  • Duplicate matches may increase row counts.
  • Incorrect WHERE conditions can unintentionally turn it into an inner join.
  • May require indexing for good performance on large tables.

20. Common Errors with LEFT OUTER JOIN

Error Cause
ORA-00904 Invalid column name
ORA-00942 Table or view does not exist
ORA-00918 Column ambiguously defined
ORA-00933 SQL command not properly ended

21. LEFT OUTER JOIN vs INNER JOIN

LEFT OUTER JOIN INNER JOIN
Returns all rows from the left table Returns only matching rows
Includes unmatched rows with NULL values Excludes unmatched rows
Used for optional relationships Used for mandatory relationships

22. LEFT OUTER JOIN vs RIGHT OUTER JOIN

LEFT OUTER JOIN RIGHT OUTER JOIN
Returns all rows from the left table Returns all rows from the right table
Missing right-side matches appear as NULL Missing left-side matches appear as NULL

23. LEFT OUTER JOIN vs FULL OUTER JOIN

LEFT OUTER JOIN FULL OUTER JOIN
Returns all rows from the left table Returns all rows from both tables
Includes matching rows and unmatched left rows Includes matching rows plus unmatched rows from both tables

24. Interview Scenario

Question: List all customers, including those who have never placed an order.

Answer: Use a LEFT OUTER JOIN.

SELECT c.customer_name,
       o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

LEFT OUTER JOIN is one of the most useful joins for reporting and identifying missing relationships in Oracle SQL.

Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)