Advanced joins and LATERAL
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
use joins beyond the basic inner/left, including LATERAL.
Planned topics
LEFT JOINkeeps unmatched left rows; conditions on the right table go inON, notWHERE, or it turns into an inner join.- Self-joins compare rows of one table (for example, product pairs bought together).
LATERALlets a subquery on the right refer to columns of the left row: a "for each row" query.- Typical use: top N per parent, with
ORDER BY ... LIMIT ninside.
Draft outline details
Demonstration
SELECT c.name, top.*
FROM category c
CROSS JOIN LATERAL (
SELECT p.name, p.price_cents FROM product p
WHERE p.category_id = c.id ORDER BY p.price_cents DESC LIMIT 3
) top;Check your understanding
What does LATERAL allow that a normal subquery in FROM does not?
Show answer
Referring to columns of earlier FROM items.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.