Intermediate LevelSQL & MySQL
What are LATERAL Joins (CROSS APPLY) and how do they pass outer query values into subqueries?
❓ Interview Prompt: Explain `LATERAL` joins (SQL standard / PostgreSQL) and `CROSS APPLY` (SQL Server), passing outer row attributes to inline subqueries, and top-N per group queries.
A 'LATERAL' Join allows a subquery in the 'FROM' clause to reference columns provided by preceding tables in the join order:
Use Case:
Executing "Top-N items per category" queries efficiently by passing each category's ID into a parameterized inline subquery.
Code Implementation Example
sql
-- Fetching top 2 most expensive products per category using LATERAL join
SELECT c.name AS category_name, p.title, p.price
FROM categories c
CROSS JOIN LATERAL (
SELECT title, price
FROM products
WHERE category_id = c.id -- References outer table c.id!
ORDER BY price DESC
LIMIT 2
) p;💡Querying top 2 products per category using a LATERAL join.
🎯 Interview Key Takeaway
Use LATERAL joins to pass outer query attributes into inline subqueries for efficient top-N per group queries.
#SQL#LATERAL#CROSS APPLY#Subqueries#Intermediate
Back to all SQL & MySQL Questions→