MySQL - SELECT * plus another field syntax

Question

With MySQL, I seem to be able to do something like this:

SELECT *, name
FROM Customers

But I get a syntax error if I reverse the order:

SELECT name, *
FROM Customers

I have tried giving the name an alias and that doesn't help. I know I could do this with a join on the same table, but I'm hoping that there is a simple syntax that will allow this to work without a self-join. Is this possible with another syntax or am I stuck with only being able to add additional query result fields after instead of before selecting all with an asterisk (*)?

"All columns" pattern allows to select additional columns/expressions. But it cannot be an addition. *but I'm hoping that there is a simple syntax that will allow this to work without a self-join* Do not use asterisk, list all column names one-by-one. — Akina, Nov 03 '20 at 16:09

Gordon Linoff · Answer 1 · 2020-11-03T18:48:30.370

1

In general, it is a good idea to qualify all column references -- even *.

So, this works in either order:

select name, c.*
from Customers c;

Or better yet:

select c.name as new_name, c.*
from Customers c;

Note: You should probably give name as different alias so there are not duplicate column names.

The one exception is when you are using the USING clause for a JOIN. Then * makes sense. When using * with USING, the JOIN key are not repeated in the SELECT columns.

edited Nov 03 '20 at 18:48

answered Nov 03 '20 at 16:09

Gordon Linoff

1,242,037
58
646
786

1

You can reference the [documentation](https://stackoverflow.com/questions/3022934/how-do-i-get-the-executing-programs-directory-in-c-using-a-plataform-independen): **Use of an unqualified `*` with other items in the select list may produce a parse error. To avoid this problem, use a qualified `tbl_name.*` reference** – Barmar Nov 03 '20 at 16:13
*The one exception is when you are using the `USING` clause for a `JOIN`. Then `*` makes sense.* Can you explain this phrase in details? why so? it looks strange.. – Akina Nov 03 '20 at 16:13
@Akina In `USING` you just list columns that are common to both tables in the join, so it doesn't make sense to qualify them with table prefixes. `USING (x)` is equivalent to `ON tbl1.x = tbl2.x` – Barmar Nov 03 '20 at 16:15
@Barmar You are wrong. Test `SELECT col, t1.col, t2.col FROM t1 LEFT JOIN t2 USING (col);`, or RIGHT JOIN. You'll see that qualified `tablename.name` is critical. The same about asterisk - without qualification you cannot distinguish does this output row has source parts in both tables or in left table only. – Akina Nov 03 '20 at 16:19
OUTER JOIN is an exception because the column can have different values when there's no match. – Barmar Nov 03 '20 at 16:21

score 1 · Accepted Answer · answered Nov 03 '20 at 16:17

MySQL tends to be more lenient that other relational database engines, but even that isn't something I'd rely on. From docs (emphasis mine):

Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference

The reliable and portable alternative would be:

SELECT name, Customers.*
FROM Customers

MySQL - SELECT * plus another field syntax

2 Answers2