Что я делаю неправильно в этом запросе выбора с левым соединением и фильтрами?

Question

С Actian PSQL v13:

Я пытаюсь объединить две таблицы и отфильтровать набор результатов по нескольким критериям. Одна таблица - история продаж клиентов. Другая таблица связывает клиентов с группами покупок. Не все клиенты входят в группу покупателей, поэтому я делаю объединение влево, чтобы получить все возможные результаты по истории продаж. Я НЕ хочу, чтобы результаты включали кого-либо в группу покупок 'SALREP', но я хочу видеть клиентов, которые не входят ни в одну из групп покупок. Кроме того, так называемая часть "FREIGHT" отфильтровывается из результатов.

У меня проблема в том, что когда я отфильтровываю группу "SALREP", выбираются только те клиенты, которые входят в группу покупок; все клиенты, не входящие в группу, отсутствуют.

Я проверил это, закомментировав фильтр для "SALREP", и набор результатов действительно включает клиентов, которые не входят в группу покупателей. Я также пытался использовать другой подход фильтра, на случай, если Actian PSQL требователен. Например, я пробовал разные методы "не равных", такие как !=, not() а также <>; проблема сохраняется. Кроме того, я использовал left join а также left outer join с той же проблемой.

Вот запрос:

select T2.GROUP_CUST, T1.DATE_INVOICE, T1.SALESPERSON, T1.CUSTOMER, T1.PRODUCT_LINE, T1.PART, T1.DESCRIPTION, T1.QTY_SHIPPED, T1.EXTENSION 
from ORDER_HIST_LINE T1 
left join BUYING_GROUP T2 on T1.CUSTOMER=T2.CUSTOMER 
where DATE_INVOICE > '2019-06-13' and PART != 'FREIGHT'
and T2.GROUP_CUST != 'SALREP' 
and T1.CUSTOMER in ('ABC', 'DEF', 'GHI')
order by T1.CUSTOMER;

Ожидаемый набор результатов должен включать в себя все, что было выставлено после 2019-06-13, не включать часть под названием "FREIGHT" и не иметь клиентов в группе под названием "SALREP". Однако фактический набор результатов является неполным. Например:

CUSTOMER | GROUP_CUST | DATE_INVOICE  | PART   | etc.
-----------------------------------------------------
ABC      |  A12       |  2019-06-14   | WIDGET
DEF      |  A12       |  2019-06-14   | GEAR

По сути, все клиенты, которые не входят ни в одну группу покупок, не учитываются.

Закомментируйте часть and T2.GROUP_CUST != 'SALREP' и ожидаемые результаты найдены. Например:

CUSTOMER | GROUP_CUST | DATE_INVOICE  | PART   | etc.
-----------------------------------------------------
ABC      |  A12       |  2019-06-14   | WIDGET
DEF      |  A12       |  2019-06-14   | GEAR
GHI      |            |  2019-06-15   | WIDGET

Я думал о создании левого соединения с запросом выбора, который в первую очередь удаляет SALREP из групп покупок, но не позволяет результирующему набору идентифицировать и удалить кого-либо из этой группы. Напр.: left join (select * from BUYING_GROUP where GROUP_CUST != 'SALREP') T2

7/19/19 Дальнейшие попытки: я нахожу ту же дилемму на MySQL v5.0.12. Я могу покинуть таблицы соединений, чтобы получить несоответствующие результаты. Я могу отфильтровать левую таблицу по этим несоответствующим результатам, ничего не потеряв. Тем не менее, я не могу отфильтровать правильную таблицу по этим несовпадающим результатам без исчезновения всех несовпадающих строк.

Answer 1

Спасибо https://mode.com/resources/sql-tutorial/sql-joins-where-vs-on/. В статье упоминается, что "фильтрация в предложении WHERE также может фильтровать нулевые значения, поэтому мы добавили дополнительную строку, чтобы убедиться, что в нее включены нулевые значения".

Это привело меня к тестированию и осознанию того, что при фильтрации по правой таблице необходимо явно указать, что значения NULL (несопоставленные строки) для этого отфильтрованного столбца должны быть включены, в противном случае эти несопоставленные строки удаляются из результирующего набора. Я также обнаружил, что необходимо заключить пару фильтров в круглые скобки или результирующий набор "взрывается". Я проверил и обнаружил, что это необходимо только при фильтрации по правой таблице; Фильтрация по левой таблице не имеет этой проблемы (очевидно).

Окончательный ответ:

select T2.GROUP_CUST, T1.DATE_INVOICE, T1.SALESPERSON, T1.CUSTOMER, T1.PRODUCT_LINE,             
T1.PART, T1.DESCRIPTION, T1.QTY_SHIPPED, T1.EXTENSION 
from ORDER_HIST_LINE T1 
left join BUYING_GROUP T2 on T1.CUSTOMER=T2.CUSTOMER 
where DATE_INVOICE > '2019-06-13' and PART != 'FREIGHT' 
and (T2.GROUP_CUST != 'SALREP' or T2.GROUP_CUST is null) 
and T1.CUSTOMER in ('ABC', 'DEF', 'GHI') 
order by T1.CUSTOMER;

Обратите внимание на линию and (T2.GROUP_CUST != 'SALREP' or T2.GROUP_CUST is null),

Answer 2

Вы должны поставить условия на втором столе в ON пункт. Единственная очевидная ссылка T2.GROUP_CUST, но это может относиться к DATE_INVOICE а также PART также:

from ORDER_HIST_LINE T1 left join
     BUYING_GROUP T2
     on T1.CUSTOMER = T2.CUSTOMER and
        T2.GROUP_CUST <> 'SALREP'
where DATE_INVOICE > '2019-06-13' and
      PART <> 'FREIGHT' and
      T1.CUSTOMER in ('ABC', 'DEF', 'GHI')
order by T1.CUSTOMER;