LEFT JOIN confusion

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)
;

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

StackExchange Database Administrators Q#246809, answer score: 4