Какое соединение SQL нужно использовать, чтобы вернуть все строки и из левой, и из правой таблицы, подставляя NULL при отсутствии пары?

Нужен FULL OUTER JOIN: он возвращает совпавшие пары и непарные строки обеих таблиц. На отсутствующей стороне поля заполняются NULL.

Пример:

SELECT l.id AS left_id, r.id AS right_id
FROM left_table AS l
FULL OUTER JOIN right_table AS r ON r.id = l.id;

Если по условию соединения несколько совпадений, результат содержит все подходящие пары, а не одну строку на ключ. Фильтр WHERE по полю отсутствующей стороны может удалить непарные строки, поэтому его нужно выбирать внимательно. Не все СУБД поддерживают FULL OUTER JOIN напрямую; тогда требуется корректная комбинация соединений.