pgSQL ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ 'ГДЕ' Условие

Описание проблемы предполагает, что вам нужен LEFT JOIN, а не FULL JOIN. FULL JOIN довольно редки, особенно в базах данных с четко определенными отношениями внешнего ключа.

В вашем случае предложение WHERE в любом случае превращает ваш FULL JOIN в LEFT JOIN, потому что предложение WHERE требует допустимых значений из первой таблицы.

SELECT spm.reference AS store_picture_monthly_reference, 
       spm.tenure, spm.name, 
       spmpr.price AS regular_price,
       spmps.price AS special_price
FROM store_picture_monthly spm INNER JOIN
     store_picture_monthly_price_regularspmpr
     ON spmpr.store_picture_monthly_reference = spm.reference LEFT JOIN
     store_picture_monthly_price_special spmps 
     ON spm.reference = spmps.store_picture_monthly_reference AND
        NOW() BETWEEN spmps.begin_date AND spmps.end_date
WHERE spmpr.effective_date < NOW();

Примечания:

• Я ввел псевдонимы таблиц, чтобы запрос было проще писать и читать.
• Условие о датах продажи теперь находится в пункте ON.
• Я удалил GROUP BY. Это не кажется необходимым. Если это так, вы можете использовать вместо него SELECT DISTINCT. И я бы исследовал проблемы с данными, если это необходимо.
• Я с подозрением отношусь к сравнению дат. NOW() имеет компонент времени. Название столбцов сравнения предполагает, что это просто даты без времени.

Каждый раз, когда вы помещаете предикат where в таблицу, которая является внешним соединением, оно преобразует внешнее соединение во внутреннее соединение, потому что нули, введенные внешним соединением, никогда нельзя сравнивать ни с чем, чтобы получить истину (поэтому внешнее соединение помещает нагрузку строк с нулями, где строки не совпадают, а затем WHERE снова выводит всю строку)

Рассмотрим этот более простой пример:

SELECT * FROM 
a LEFT JOIN b ON a.id = b.id
WHERE b.col = 'value'

Идентичен:

SELECT * FROM 
a INNER JOIN b ON a.id = b.id
WHERE b.col = 'value'

Чтобы решить эту проблему, переместите предикат из where в ON

SELECT * FROM 
a LEFT JOIN b ON a.id = b.id AND b.col = 'value'

Вы также можете рассмотреть:

SELECT * FROM 
a LEFT JOIN b ON a.id = b.id
WHERE b.col = 'value' OR b.col IS NULL

но это может собрать данные, которые вам не нужны, если b.col естественно содержит некоторые нули; он не может различать нули, которые изначально присутствуют в b.col, и нули, которые появляются из-за сбоя в соединении, чтобы сопоставить строку из b со строкой из a (если мы также не посмотрим на нулевое значение объединенного столбца id)

A
id
1
2
3

B
id, col
1, value
3, null

--wrong, behaves like inner
A left join B ON a.id=b.id WHERE b.col = 'value'
1, 1, value

--maybe wrong, b.id 3 might be unwanted
A left join B ON a.id=b.id WHERE b.col = 'value' or b.col is null
1, 1, value
2, null, null
3, 3, null

--maybe right, simpler to maintain than the above
A left join B ON a.id=b.id AND b.col = 'value' 
1, 1, value
2, null, null
3, null, null

В этих двух последних разница заключается в том, что b.id имеет значение null или нет, хотя количество строк одинаково. Если бы мы считали b.id, наш подсчет мог бы закончиться неправильно. Важно понимать этот нюанс поведения соединения. Возможно, вам даже понадобится это, если вы хотите исключить строку 3, но включить строку 2, создав предложение where из a LEFT JOIN b ON a.id=b.id WHERE b.col = 'value' OR b.id IS NULL — это сохранит строку 2, но исключит строку 3, потому что даже если соединение успешно находит b.id 3 он не сохраняется ни одним из предикатов