ruk·si

🐘 PostgreSQL
LATERAL joins

Updated at 2016-01-01 18:09

Joins can be marked as LATERAL. Lateral joins can access data from previously executed joins, which isn't normally possible as normal join queries are executed independently.

CREATE TABLE restaurants (
    restaurant_id       uuid        NOT NULL DEFAULT uuid_generate_v4(),
    name                text        NOT NULL,
    CONSTRAINT restaurants_pkey  PRIMARY KEY (restaurant_id)
);

CREATE TABLE inspections (
    inspection_id       uuid        NOT NULL DEFAULT uuid_generate_v4(),
    restaurant_id       uuid        NOT NULL,
    inspected_at        timestamp   NOT NULL,
    CONSTRAINT inspections_pkey  PRIMARY KEY (inspection_id)
);

INSERT INTO restaurants (name)
VALUES ('Apizza'), ('Bergers'), ('Calads');

INSERT INTO inspections (restaurant_id, inspected_at) VALUES
((SELECT restaurant_id FROM restaurants WHERE name = 'Apizza'), '2015-08-07'),
((SELECT restaurant_id FROM restaurants WHERE name = 'Apizza'), '2015-06-17'),
((SELECT restaurant_id FROM restaurants WHERE name = 'Apizza'), '2016-01-01'),
((SELECT restaurant_id FROM restaurants WHERE name = 'Apizza'), '2015-04-04'),
((SELECT restaurant_id FROM restaurants WHERE name = 'Calads'), '2015-02-01'),
((SELECT restaurant_id FROM restaurants WHERE name = 'Apizza'), '2015-02-22'),
((SELECT restaurant_id FROM restaurants WHERE name = 'Calads'), '2015-02-22');
Now we have 3 restaurants: Apizza, Bergers and Calads.
And a bunch of inspections related to Apizza and Calads.
-- list all restaurants with details of their most recent inspection if exists
SELECT *
FROM restaurants r
LEFT JOIN LATERAL (
    SELECT *
    FROM inspections
    WHERE restaurant_id = r.restaurant_id
    ORDER BY inspected_at DESC
    LIMIT 1
) i ON true;
-- Returns 3 rows, but Bergers has NULL inspection detail columns.

-- Without LATERAL, the query returns:
-- HINT:  There is an entry for table "r", but it cannot be referenced
-- from this part of the query.

Sources