How to override column from Table A with JOIN mapping from Table B?-CodePudding

Is it possible to overlay a column with another table, via JOIN mapping?

Table A:

int id;
varchar longname;
varchar type;

Table B:

varchar type;
varchar longname;

Sql:

select id, type, tableB.longname from tableA
   LEFT JOIN tableB on tableA.type = tableB.type;

Result: longname is always null if there is no mapping in tableB.

Question: how could I retain longname from tableA if there is no mapping in tableB?

CodePudding user response：

select id, type, COALESCE(tableB.longname, tableA.longname) AS longname 
from tableA
LEFT JOIN tableB on tableA.type = tableB.type;