Let's say I've got two tables:
Table1:
| post_id | text |
|---|---|
| 1 | data |
Table2:
| post_id | user_id | read |
|---|---|---|
| 1 | 1 | true |
And I'm selecting data as:
SELECT
t1.post_id,
t1.text,
t2.user_id,
t2.read
FROM
table1 t1
JOIN table2 t2 ON t2.post_id = t1.post_id
AND t2.user_id IN (1, 2, 3)
That would obviously net me one row with [post_id = 1, text = data, user_id = 1, read = true]. What I want is to "generate" rows on fly with default values for other user entries that don't actually exist in table2.
To be precise, this is what I would like as a result:
| post_id | text | user_id | read |
|---|---|---|---|
| 1 | data | 1 | true |
| 1 | data | 2 | false |
| 1 | data | 3 | false |
CodePudding user response：
Use a CROSS JOIN to generate all pairs of posts and users, then use a LEFT JOIN:
SELECT table1.post_id, users.user_id, COALESCE(table2.read, false)
FROM table1
CROSS JOIN (VALUES (1), (2), (3)) AS users(user_id) -- you can use an actual table here
LEFT JOIN table2 ON table1.post_id = table2.post_id AND users.user_id = table2.user_id