How to replace table1 column value with table2 column value in MySQL select left join

Let’s say I have two MySQL tables:

table1 (templates) Columns: id_template, name, col3, col4, …

table2 (aliases) Columns: id_alias, id_template_orig, id_user, alias

I want to select a row from the table1 (templates) including all the columns (*). In the same select statement I want to check if current user has saved an alias for the original template name in the table2 (aliases). There is no problem getting the alias column included in the result row with LEFT JOIN but I wonder if there is a way to replace the original name column value already selected from table1 with the table2 alias value.

So here is the select so far:

SELECT table1.*, table2.alias, 
CASE WHEN table2.alias IS NULL THEN table1.name ELSE table2.alias END name 
FROM table1
LEFT JOIN table2 ON table2.id_template_orig=table1.id_template 
WHERE table1.id_template='1' 

This works almost fine except that it returns two name columns instead of replacing the first with the second

I know that it is not the best practice to select all the columns with table1.* and my goal would be achieved by instead using select id_template, col3, col4, … from table1 but sometimes * just makes things much easier.

Answer

Unfortunately, there is no option in MySQL to select * but one column – as in BigQuery for example, that supports select * except (name).

You have two options:

• don’t use select table1.*; instead, enumerate all the columns you want, expect name

• use select *, and give a different alias to the generated column.

The second option might be your best pick, since you clearly stated that you want to keep select table1.*. It would look like:

select t1.*, t2.alias, coalesce(t2.alias, t1.name) as new_name 
from table1 t1
left join table2 t2 on t2.id_template_orig = t1.id_template 
where t1.id_template = 1 

Notes:

• coalesce() can just replace your case expression here

• table aliases make the query easier to write and read