Show all data from the second table overriding with the same data in the first table-CodePudding

I would like to get the user_settings fields (that are in the default_settings) and the default_settings fields that don't exists in the user_settings. As per result below

default_settings

field	value
Name	Test
Age	21
Sex	M
Dob

user_settings

field	value
Name	Jack
Age	40

result =>

field	value
Name	Jack
Age	40
Sex	M
Dob

SELECT ds.field, ds.value
FROM user_settings AS us
LEFT JOIN default_settings AS ds ON ds.field != us.field

CodePudding user response：

I would write it this way:

SELECT ds.field, COALESCE(us.value, ds.value) AS value
FROM default_settings AS ds
LEFT JOIN user_settings AS us ON ds.field = us.field;