LEFT JOIN confusion

Question

I am having issues with this simple scenario. I'm probably missing some basic concept...

I have 2 tables, the first of "things" and the second of orders. I've created a sqlfiddle: http://sqlfiddle.com/#!17/e9d19/6/0

Query:

select t1.*, t2.*
from things t1
left join things_orders t2
on t1.id = t2.thing_id

I want to have the results show a row for each item and person. In my example, Adam ordered an apple, 2 bananas and 3 cherries. Ben ordered no apples (no row in the database), 2 bananas and 3 cherries. I want the result to have 6 rows (simplified output):

Apple Adam 1
Banana Adam 2
Cherry Adam 3
Apple Ben null <-- wanted row, but not showing
Banana Ben 2
Cherry Ben 3

I didn't think I needed to have a row in the second table with a null value but maybe I do.

This is the DDL which is also in the sqlfiddle:

CREATE TABLE things (
    id smallint NOT NULL,
    name text COLLATE pg_catalog."default",
    CONSTRAINT things_pkey PRIMARY KEY (id)
);

CREATE TABLE things_orders (
    person text COLLATE pg_catalog."default" NOT NULL,
    thing_id smallint NOT NULL,
    qty integer,
    CONSTRAINT things_orders_pk PRIMARY KEY (person, thing_id)
);

INSERT INTO things VALUES 
  (1, 'Apple')
, (2, 'Banana')
, (3, 'Cherry')
;

INSERT INTO things_orders VALUES
  ('Adam', 1, 1)
, ('Adam', 2, 2)
, ('Adam', 3, 3)
, ('Ben', 2, 2)
, ('Ben', 3, 3)
;

Akina · Answer 1 · 2019-09-03T06:10:10.630

You have the list of foods. So you'll obtain all of them (using left join), even they are not present on orders.

But you have NO the list of persons. So if you want to obtain all of them even they are not present on orders you must build their list. In subquery or in CTE.

Having 2 lists you can buils full list of pairs (food-person). Then you'll left join orders to that list - it will give the result you need.

SELECT t0.person, t1.name, t2.qty
FROM ( SELECT DISTINCT person 
       FROM things_orders ) t0
CROSS JOIN things t1
LEFT JOIN things_orders t2 ON t1.id = t2.thing_id 
                          AND t0.person = t2.person

Erwin Brandstetter · Accepted Answer · 2019-09-04T12:39:48.080

Typically, you'd also have a table people holding exactly one row for every relevant person. ('Ben' like in the example is hardly unique.) If you don't, consider creating it. Burns down to a standard many-to-many relationship:

How to implement a many-to-many relationship in PostgreSQL?

Then build the Cartesian product from people and things with a CROSS JOIN, and LEFT JOIN to things_orders:

SELECT p.id AS person_id, p.person, t.id as thing_id, t.name as thing, tp.qty
FROM   people p
CROSS  JOIN things t
LEFT   JOIN things_orders tp ON tp.thing_id  = t.id
                            AND tp.person_id = p.id;

Fill missing dates within groups

Meanwhile, if things_orders is big and has many rows per person, getting unique persons with plain DISTINCT is expensive. And index on (person) helps but not much while Postgres does not yet support index skip scans. (Did not make it into Postgres 12, maybe in Postgres 13 ...)

Until then, a recursive CTE is the fast workaround:

WITH RECURSIVE persons AS (
   (
   SELECT person
   FROM   things_orders
   ORDER  BY person
   LIMIT  1
   )
   UNION ALL
   SELECT x.person
   FROM   persons p
   CROSS  JOIN LATERAL (
      SELECT person
      FROM   things_orders
      WHERE  person > p.person
      ORDER  BY person
      LIMIT  1
      ) x
   )
SELECT p.person, t.thing_id, t.name, tp.qty
FROM   persons p
CROSS  JOIN (SELECT id AS thing_id, name FROM things) t
LEFT   JOIN things_orders tp USING (thing_id, person);

db<>fiddle here

You may want COALESCE(tp.qty, 0) to show 0 instead of NULL ...

LEFT JOIN confusion

2 Answers2