Oracle LATERAL JOIN FAQs with Examples

1. What is a LATERAL JOIN in Oracle?

A LATERAL JOIN allows a subquery in the FROM clause to reference columns from tables that appear before it in the same FROM clause.

Without LATERAL, a subquery in the FROM clause cannot reference columns from preceding tables.

Introduced in Oracle Database 12c, LATERAL is useful for correlated subqueries that return multiple rows.

Syntax

SELECT column_list
FROM table1 t1,
LATERAL (
  SELECT ...
  FROM table2 t2
  WHERE t2.column_name = t1.column_name
);

2. Why do we use a LATERAL JOIN?

A LATERAL JOIN is used to:

  • Reference columns from preceding tables inside a subquery.
  • Return multiple related rows for each row in the outer table.
  • Simplify correlated subqueries.
  • Retrieve Top-N rows per group.
  • Improve readability compared to complex nested queries.

3. How does a LATERAL JOIN work?

Suppose we have:

DEPARTMENTS

DEPARTMENT_ID   DEPARTMENT_NAME
10              Sales
20              HR

EMPLOYEES

FIRST_NAME   DEPARTMENT_ID   SALARY
John         10              5000
David        10              4500
Scott        20              7000

Query

SELECT d.department_name,
       e.first_name,
       e.salary
FROM departments d,
LATERAL (
  SELECT first_name,
         salary
  FROM employees e
  WHERE e.department_id = d.department_id
) e;

Output

Department   Employee   Salary
Sales        John       5000
Sales        David      4500
HR           Scott      7000

4. What is the syntax of a LATERAL JOIN?

SELECT *
FROM table1 t1,
LATERAL (
  SELECT *
  FROM table2 t2
  WHERE t2.id = t1.id
);

5. What problem does LATERAL solve?

Without LATERAL, the following query is invalid because the inline view cannot reference d.department_id:

SELECT *
FROM departments d,
(
  SELECT *
  FROM employees e
  WHERE e.department_id = d.department_id
);

With LATERAL, it becomes valid:

SELECT *
FROM departments d,
LATERAL (
  SELECT *
  FROM employees e
  WHERE e.department_id = d.department_id
);

6. Can LATERAL return multiple rows?

Yes. Unlike a scalar subquery, a LATERAL subquery can return zero, one, or many rows for each row in the outer table.

SELECT d.department_name,
       e.first_name
FROM departments d,
LATERAL (
  SELECT first_name
  FROM employees e
  WHERE e.department_id = d.department_id
) e;

7. Can LATERAL use ORDER BY?

Yes.

SELECT d.department_name,
       e.first_name,
       e.salary
FROM departments d,
LATERAL (
  SELECT first_name,
         salary
  FROM employees e
  WHERE e.department_id = d.department_id
  ORDER BY salary DESC
) e;

8. Can LATERAL be used to fetch the Top-N rows per group?

Yes.

Example: Highest-paid employee in each department.

SELECT d.department_name,
       e.first_name,
       e.salary
FROM departments d,
LATERAL (
  SELECT first_name,
         salary
  FROM employees e
  WHERE e.department_id = d.department_id
  ORDER BY salary DESC
  FETCH FIRST 1 ROW ONLY
) e;

Output

Department   Employee   Salary
Sales        John       5000
HR           Scott      7000

9. Can LATERAL be used with LEFT OUTER JOIN?

Yes. Oracle supports LEFT OUTER JOIN LATERAL.

SELECT d.department_name,
       e.first_name
FROM departments d
LEFT JOIN LATERAL (
  SELECT first_name
  FROM employees e
  WHERE e.department_id = d.department_id
) e
ON 1 = 1;

This returns all departments, even those without employees.

10. Can LATERAL be used with CROSS JOIN?

Yes.

SELECT d.department_name,
       e.first_name
FROM departments d
CROSS JOIN LATERAL (
  SELECT first_name
  FROM employees e
  WHERE e.department_id = d.department_id
) e;

CROSS JOIN LATERAL returns only departments for which the lateral subquery returns rows.

11. Can LATERAL use aggregate functions?

Yes.

SELECT d.department_name,
       x.avg_salary
FROM departments d,
LATERAL (
  SELECT AVG(salary) AS avg_salary
  FROM employees e
  WHERE e.department_id = d.department_id
) x;

12. Can LATERAL use GROUP BY?

Yes.

SELECT d.department_name,
       x.employee_count
FROM departments d,
LATERAL (
  SELECT COUNT(*) AS employee_count
  FROM employees e
  WHERE e.department_id = d.department_id
  GROUP BY department_id
) x;

13. Can LATERAL use analytic functions?

Yes.

SELECT d.department_name,
       x.first_name,
       x.salary_rank
FROM departments d,
LATERAL (
  SELECT first_name,
         RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees e
  WHERE e.department_id = d.department_id
) x;

14. What is the difference between LATERAL and a normal inline view?

Normal Inline View LATERAL
Cannot reference previous tables Can reference previous tables
Independent subquery Correlated subquery
Less flexible More flexible

15. What is the difference between LATERAL and a correlated subquery?

Correlated Subquery LATERAL
Usually in the SELECT or WHERE clause Appears in the FROM clause
Often returns one value Can return multiple rows
Limited flexibility More flexible for complex row sets

16. What are common uses of LATERAL?

  • Top-N rows per group.
  • Latest order per customer.
  • Highest-paid employee per department.
  • JSON processing.
  • XML processing.
  • Table functions that depend on each input row.

17. Real-Time Example – Latest Order Per Customer

CUSTOMERS

CUSTOMER_ID   CUSTOMER_NAME
1             Alice
2             Bob

ORDERS

ORDER_ID   CUSTOMER_ID   ORDER_DATE
101        1             2025-01-01
102        1             2025-03-10
103        2             2025-02-15

Query

SELECT c.customer_name,
       o.order_id,
       o.order_date
FROM customers c,
LATERAL (
  SELECT order_id,
         order_date
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY order_date DESC
  FETCH FIRST 1 ROW ONLY
) o;

Output

Customer   Latest Order
Alice      102
Bob        103

18. What are the advantages of LATERAL?

  • Supports correlated inline views.
  • Can return multiple rows.
  • Simplifies complex SQL.
  • Excellent for Top-N queries.
  • Works well with table functions.
  • Improves readability.

19. What are the disadvantages of LATERAL?

  • Available only in Oracle Database 12c and later.
  • Can be harder to understand for developers unfamiliar with it.
  • Poorly written correlated subqueries may affect performance on large datasets.

20. Common Errors with LATERAL

Error Cause
ORA-00904 Invalid column name
ORA-00942 Table or view does not exist
ORA-00933 SQL command not properly ended
ORA-00907 Missing right parenthesis

21. LATERAL vs CROSS JOIN

LATERAL CROSS JOIN
Can reference preceding tables Cannot reference preceding tables
Correlated subquery Cartesian product
Returns related rows Returns every possible combination

22. LATERAL vs CROSS APPLY

Oracle supports CROSS APPLY and OUTER APPLY, which are closely related to LATERAL.

LATERAL CROSS APPLY
SQL standard syntax Oracle/Microsoft-style syntax
Correlated inline view Correlated table expression
Returns matching rows Similar behavior to CROSS JOIN LATERAL

Example using CROSS APPLY:

SELECT d.department_name,
       e.first_name
FROM departments d
CROSS APPLY (
  SELECT first_name
  FROM employees e
  WHERE e.department_id = d.department_id
) e;

23. LATERAL vs APPLY

Feature LATERAL CROSS APPLY OUTER APPLY
Returns only matching rows Yes Yes No
Returns unmatched outer rows No No Yes
Correlated subquery Yes Yes Yes

OUTER APPLY behaves similarly to a LEFT OUTER JOIN LATERAL.

24. Interview Scenario

Question: Display the highest-paid employee in every department.

Answer: Use a LATERAL JOIN with FETCH FIRST 1 ROW ONLY.

SELECT d.department_name,
       e.first_name,
       e.salary
FROM departments d,
LATERAL (
  SELECT first_name,
         salary
  FROM employees e
  WHERE e.department_id = d.department_id
  ORDER BY salary DESC
  FETCH FIRST 1 ROW ONLY
) e;

LATERAL is useful when a subquery must depend on rows from tables that appear earlier in the FROM clause.

Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)