Preferably get non-null columns in a SQL join-CodePudding

I have a query that gets the data from two tables (TABLE_ONE left join TABLE_TWO) that have the same columns. I need to get the data from TABLE_ONE, but if the data is null and in TABLE_TWO it is not null, then the column of TABLE_TWO is returned.

TABLE_ONE

data1	data2	data3	data4
car		red	4
bike		blue

TABLE_TWO

data1	data2	data3	data4
car	spain		7
bike		blue	9

This is the query:

select distinct *
from TABLE_ONE T1
left join TABLE_TWO T2 on T1.data1 = T2.data1
  and (T1.data2 = T2.data2 OR (T1.data2 like '' OR T2.data2 like ''))
  and (T1.data3 = T2.data3 OR (T1.data3 like '' OR T2.data3 like ''))

This is what I get:

data1	data2	data3	data4	data1	data2	data3	data4
car		red	4	car	spain	red	7
bike		blue		bike		blue	9

This is what I need:

data1	data2	data3	data4
car	spain	red	4
bike		blue	9

Is it possible to merge the columns that have the same name?, that is, a different column, but that are not null, if both columns are null, take the one from TABLE_ONE

CodePudding user response：

Use coalesce

Select
  coalesce(t1.data1, t2.data1) Data1,
  coalesce(t1.data2, t2.data2) Data2,
  coalesce(t1.data3, t2.data3) Data3,
  coalesce(t1.data4, t2.data4) Data4
From...

CodePudding user response：

You need a full outer join here. MySQL does not directly support them, but we can emulate them via a union query:

SELECT t1.data1,
       COALESCE(t1.data2, t2.data2) AS data2,
       COALESCE(t1.data3, t2.data3) AS data3,
       COALESCE(t1.data4, t2.data4) AS data4
FROM TABLE_ONE t1
LEFT JOIN TABLE_TWO t2 ON t2.data1 = t1.data1
UNION ALL
SELECT t2.data1, t2.data2, t2.data3, t2.data4
FROM TABLE_ONE t1
RIGHT JOIN TABLE_TWO t2 ON t2.data1 = t1.data1
WHERE t1.data1 IS NULL;