In MySQL, this code works fine:
select f, blegg.* from blegg limit 1;
+------+------+------+------+
| f | f | g | h |
+------+------+------+------+
| 17 | 17 | 2 | 17 |
+------+------+------+------+
1 row in set (0.00 sec)
So why does this code cause a syntax error?
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've looked through the manual but didn't really find anything. Why does select <field>, * ...
fail where select <field>, <table>.* ...
and select * ...
and select *, <field> ...
succeed?
This SQL error generally means that somewhere in the query, there is invalid syntax. Some common examples: Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD) Typo in the SQL (missing comma, misspelled word, etc)
People uses `` to surround field names and use '' to surround values. Is that true? Or the major reason to do so is because then we can put the code in a text file and import into the database. thank you. mysql.
The SELECT clause specifies which values are to be returned. To display all the columns of a table, use the asterisk wildcard character (*). For example, the following query displays all rows and columns from the employees table: SELECT * FROM employee; To select specific columns, specify the column names.
Since MySQL does not provide support for the INTERSECT operator. However, we can use the INNER JOIN and IN clause to emulate this operator.
The MySQL manual lays all this out pretty clearly in the section on SELECT
syntax:
A select list consisting only of a single unqualified
*
can be used as shorthand to select all columns from all tables:SELECT * FROM t1 INNER JOIN t2 ...
tbl_name.*
can be used as a qualified shorthand to select all columns from the named table:SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...
Use of an unqualified
*
with other items in the select list may produce a parse error. To avoid this problem, use a qualifiedtbl_name.*
referenceSELECT AVG(score), t1.* FROM t1 ...
The documentation seems to indicate that *
by itself is only valid in the special case where it's the only thing in the select list. However, it only says using an unqualified *
with other items may produce a parse error.
Beyond MySQL, the SQL-92 standard (old, but linkable) says as much:
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 either be <asterisk>
by itself or a "normal" select list.
but
select *, f from blegg
will work fine.
Possibly an unqualified * has to appear as the first expression in the select?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With