Geek Answers Handbook

Why does adding a * to a MySQL query cause a syntax error?

Why does this cause a syntax error (MySQL 5)?

mysql> select f, blegg.* from blegg limit 1; +------+------+------+------+ | f | f | g | h | +------+------+------+------+ | 17 | 17 | 2 | 17 | +------+------+------+------+ 1 row in set (0.00 sec) mysql> select f, * from blegg limit 1; -- * is unqualified ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from blegg limit 1' at line 1

I looked through the manual but found nothing. Why does select <field>, * ... fail to execute select <field>, <table>.* ... and select * ... and select *, <field> ... ?

+4
source share
3 answers

In the MySQL manual, all of this is pretty clearly stated in the SELECT syntax section:

  • A selection list consisting of only one unqualified * can be used as a shorthand to select all columns from all tables:

     SELECT * FROM t1 INNER JOIN t2 ...

  • tbl_name.* can be used as qualified shorthand to select all columns from the named table:

     SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

  • Using unqualified * with other items in the selection list may cause a parsing error. to avoid this problem, use the qualified tbl_name.* link tbl_name.*

     SELECT AVG(score), t1.* FROM t1 ...

The documentation seems to indicate that * by itself is only valid in the special case where it is the only one on the select list. However, he only says that using unqualified * with other elements can lead to a parsing error.

In addition to MySQL, the SQL-92 standard (old but related) says this:

 7.9 <query specification> Format <query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression> <select list> ::= <asterisk> | <select sublist> [ { <comma> <select sublist> }... ] <select sublist> ::= <derived column> | <qualifier> <period> <asterisk> <derived column> ::= <value expression> [ <as clause> ] <as clause> ::= [ AS ] <column name>

<select list> can be either <asterisk> on its own, or a "normal" selection list.

+7
source

but

 select *, f from blegg

will work fine.

Perhaps an unskilled * should appear as the first expression in select?

+1
source

Perhaps because you select the same field twice. In the next query

 select name, * from <...>

* will include name , so you explicitly specify name second time.

This is not a convincing argument, as the following are acceptable:

 select name, name from <...>

as well as the following

 select name, users.* from users

both of them will select the same field several times.

This is most likely just a syntax limitation of MySQL.

-2
source

More articles:


All Articles