LATERAL JOINs in PostgreSQL

The topic of JOINs was thoroughly covered (JOINs are old news), but one user (Mani) made this comment:

I have gone through videos, but lateral join concept is missing. Please update course with lateral join and use case.

This prompted a digression into LATERAL JOINs as it was a new thing to me.

My reply is as follows:

Many thanks to Mani for mentioning LATERAL JOINs, I’d never hear of them.

The PostgreSQL documentation on the topic is here: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL

Here’s an example script I wrote for psql:

DROP TABLE IF EXISTS public.lat_join_manufacturers;

CREATE TABLE public.lat_join_manufacturers (
  id integer GENERATED BY DEFAULT AS IDENTITY,
  name varchar
  );

INSERT INTO public.lat_join_manufacturers (id, name) VALUES
  (1, 'ABC Ltd'),
  (2, 'XYZ Ltd'),
  (3, 'Defunct LLC')
  RETURNING *
  ;

DROP TABLE IF EXISTS public.lat_join_products;

CREATE TABLE public.lat_join_products (
  id integer GENERATED BY DEFAULT AS IDENTITY,
  name varchar,  manufacturer_id integer
  );

INSERT INTO public.lat_join_products (
  id, name,
  manufacturer_id
  ) VALUES
  (1, 'fork', 1),
  (2, 'spoon', 1)
  RETURNING *
  ;

DROP FUNCTION IF EXISTS public.fn_lat_join_get_products;

CREATE OR REPLACE FUNCTION public.fn_lat_join_get_products(
  man_id integer
  )
  RETURNS varchar
  AS $$

    SELECT
      name
    FROM
      public.lat_join_products AS p
    WHERE
      p.manufacturer_id = man_id

   $$
  LANGUAGE SQL ;


\echo Find manufacturers with NO products:

SELECT  *
FROM  public.lat_join_manufacturers AS m
INNER JOIN LATERAL fn_lat_join_get_products(m.id) AS pname
  ON TRUE
WHERE  pname IS NULL
  ;

The benefit of LATERAL JOINs is the second query can refer to columns from the first query, especially handy when running a UDF (User Defined Function) on one of those columns.

Here’s the main output of that script, run in psql:

Find manufacturers with NO products:

[...snipped logging output...]
  id |    name     | pname
----+-------------+--------
  2 | XYZ Ltd     | (null)
  3 | Defunct LLC | (null)
(2 rows)