Сохраняйте нулевые отношения в WHERE IN() или с помощью SELECT и LEFT JOIN

У меня есть такая таблица:

 table:
 | id | fkey | label | amount |
 |----|------|-------|--------|
 | 1  | 1    | aaa   | 10     |
 | 2  | 1    | bbb   | 15     |
 | 3  | 1    | fff   | 99     |
 | 4  | 1    | jjj   | 33     |
 | 5  | 2    | fff   | 10     |

fkey является внешним ключом к другой таблице.

Теперь мне нужно запросить все суммы, связанные с некоторыми метками ('bbb', 'eee', 'fff') и с указанным fkey, но мне нужно сохранить все несуществующие метки с NULL.

Для простого запроса с WHERE IN ('bbb', 'eee', 'fff') я получил, конечно, только две строки:

 SELECT label, amount FROM table WHERE label IN ('bbb', 'eee', 'fff') AND fkey = 1;

 | label | amount |
 |-------|--------|
 | bbb   | 15     |
 | fff   | 99     |

но исключенный результат должен быть:

 | label | amount |
 |-------|--------|
 | bbb   | 15     |
 | eee   | NULL   |
 | fff   | 99     |

Я также пробовал SELECT label UNION ALL label (...) LEFT JOIN, который должен работать на MySQL (Сохранять все записи в предложении WHERE IN(), даже если они не найдены):

  SELECT T.label, T.amount FROM (
    SELECT 'bbb' AS "lbl"
    UNION ALL 'eee' AS "lbl"
    UNION ALL 'fff' AS "lbl"
  ) LABELS
  LEFT OUTER JOIN table T ON (LABELS."lbl" = T."label")
  WHERE T.fkey = 1;

а также с оператором WITH:

  WITH LABELS AS (
    SELECT 'bbb' AS "lbl"
    UNION ALL 'eee' AS "lbl"
    UNION ALL 'fff' AS "lbl"
  )
  SELECT T.label, T.amount FROM LABELS
  LEFT OUTER JOIN table T ON (LABELS."lbl" = T."label")
  WHERE T.fkey = 1;

но всегда это LEFT JOIN дает мне 2 строки вместо 3.

Создание временной таблицы вообще не работает (у меня из нее 0 строк, и я не могу использовать это в соединении):

 CREATE TEMPORARY TABLE __ids (
   id VARCHAR(9) PRIMARY KEY
 ) ON COMMIT DELETE ROWS;

 INSERT INTO __ids (id) VALUES
   ('bbb'),
   ('eee'),
   ('fff');

 SELECT
   *
 FROM __ids

Любая идея, как заставить Postgres сохранять пустое отношение? Или даже любая другая идея получить метку «eee» с суммой NULL, если для этого нет строки в таблице?

Список меток может быть разным для каждого запроса.

Этот кейс онлайн: http://rextester.com/CRQY46630

------ РЕДАКТИРОВАТЬ ----- Я расширил этот вопрос с помощью фильтра where, потому что ответ от a_horse_with_no_name великолепен, но не охватывает весь мой случай (я предполагал, что это where не имеет значения)


postgresql sql postgresql-9.5 outer-join
person bodolsog    schedule 20.03.2017    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Ваш подход с внешним соединением действительно работает. Вам просто нужно взять значение метки из «внешней» объединенной таблицы, а не из «таблицы»:

with labels (lbl) as (
  values ('bbb'), ('eee'), ('fff')
)
select l.lbl,  --<< this is different to your query
       t.amount 
from labels l 
  left outer join "table" t on l.lbl = t.label;

Онлайн-пример: http://rextester.com/LESK82163

Изменить после того, как объем вопроса был расширен.

Если вы хотите фильтровать базовую таблицу, вам нужно переместить в условие JOIN, а не в предложение where:

with labels (lbl) as (
  values ('bbb'), ('eee'), ('fff')
)
select l.lbl,  --<< this is different to your query
       t.amount 
from labels l 
  left outer join "table" t 
               on l.lbl = t.label
              and t.fkey = 1; --<<

Онлайн-пример: http://rextester.com/XDO76971

person a_horse_with_no_name    schedule 20.03.2017