Search code examples
sqlsql-server

Query to return fixed rows even if matching rows missing in join

In SQL Server I have Table1 with rows as follows

Col 1 Col 2
A Andy
B Becky
C Carol

In Table2 I have following rows

Col 1 Col 2 Col 3
A Low 3
A Medium 2
A High 5
B High 7
C Low 1
C High 12

Using a join I want to out rows for all 3 values (Low, Medium, High) even if its missing from Table2

So essentially I want to get

Col 1 Col 2 Col 3
A Low 3
A Medium 2
A High 5
B Low NULL
B Medium NULL
B High 7
C Low 1
C High 12
C Medium NULL

How can I write my join to return fixed rows even when missing in Table2?

Solution

  • You can cross-join Table1 with a constructed table of those three values (or a real table if you have one), then left join Table2.

    SELECT
  t1.Col1,
  v.Col2,
  t2.Col3
FROM Table1 t1
CROSS JOIN (VALUES
  ('Low'),
  ('Medium'),
  ('High')
) v(Col2)
LEFT JOIN Table2 t2
  ON t2.Col1 = t1.Col1
  AND t2.Col2 = v.Col2;

    db<>fiddle