PostgreSQL

PostgreSQL FULL JOIN

Keep matching and unmatched rows from both tables.

Use FULL JOIN

SELECT p.name, c.name AS category
FROM products AS p
FULL JOIN categories AS c
  ON c.id = p.category_id;

When a row has no match, the columns from the other table contain NULL.