sqlvectorwise

Return rows for IN query with failed look-ups returning NULL

Given this query:

SELECT a, b FROM c WHERE a IN ('v1', 'v2', 'v3');

If table c had values for v1 and v2 but not v3, I'd get a result set of 2 rows. Is there a way I can perform the same query (or one that yields the same outcome) while getting a row for every target?

In this case, I'd like to receive 3 rows, 2 with the values for 'v1' and 'v2' and one with a NULL.

Solution

  • Not sure if this will work on your particular RDBMS, but here's how I would approach it:

    select x.a, c.b
from (
  select 'v1' as a union all
  select 'v2' as a union all
  select 'v3' as a
) x
left outer join c on c.a = x.a

    Create a temporary table and use it to left-join in your desired table.