How to default column to default value / value of another column if no matching row in SQL Left Join-CodePudding

I have a table Table_A with a name and a field named type_and_cargo. The following Postgresql query uses a left join to get the separate "type" and "cargo" values from Table_B

SELECT 
a.type_and_cargo,
a.name
b.type_and_cargo,
b.type,
b.cargo FROM table_A a
LEFT JOIN table_B b USING (type_and_cargo)

But some rows in Table_A have a type_and_cargo which does not have a corresponding value in Table_B. Right now, the "type" and "cargo" columns become Null. How can I assign the a.type_and_cargo to "type" and Unknown to "cargo" if not match exists?

CodePudding user response：

Would this do it?

SELECT 
  a.type_and_cargo,
  a.name
  b.type_and_cargo,
  CASE
    WHEN b.type_and_cargo IS NULL THEN a.type_and_cargo
    ELSE b.type
  END as type
  CASE
    WHEN b.type_and_cargo IS NULL THEN 'Unknown'
    ELSE b.cargo
  END as cargo
FROM table_A a
  LEFT JOIN table_B b USING (type_and_cargo)