Keep null relations on WHERE IN() or with SELECT and LEFT JOIN

Question

I have table like there:

 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 is a foreign key to other table.

Now I need to query for all amounts asociated with some labels ('bbb', 'eee', 'fff') and with specifed fkey, but i need to keep all unexisting labels with NULL.

For simple query with WHERE IN ('bbb', 'eee', 'fff') I got, of course, only two rows:

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

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

but excepted result should be:

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

I tried also SELECT label UNION ALL label (...) LEFT JOIN which should work on MySQL (Keep all records in "WHERE IN()" clause, even if they are not found):

  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;

and also with WITH statement:

  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;

but always this LEFT JOIN give me 2 rows istead 3.

Create temporary table doesn't work at all (I got 0 rows from it and I cannot use this in join):

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

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

 SELECT
   *
 FROM __ids

Any idea how to enforce Postgres to keep empty relation? Or even any other idea to get label 'eee' with NULL amount if there is not row for this in table?

List of labels can be different on every request.

This case online: http://rextester.com/CRQY46630

------ EDIT ----- I extended this question with filter where, because answer from a_horse_with_no_name is great, but not cover my whole case (I supposed this where no matter there)

score 3 · Accepted Answer · 2017-03-20T23:28:46.427

Your approach with the outer join does work. You just need to take the label value from the "outer" joined table, not from the "table":

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;

Online example: http://rextester.com/LESK82163

Edit after the scope of the question was extended.

If you want to filter on the base table, you need to move the into the JOIN condition, not the where clause:

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; --<<

Online example: http://rextester.com/XDO76971

Keep null relations on WHERE IN() or with SELECT and LEFT JOIN

1 Answers1