Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

select

mysql

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?

like image 612
Matt Fenwick Avatar asked Oct 27 '11 20:10

Matt Fenwick


People also ask

Why am I getting a syntax error in SQL?

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)

What is `` in MySQL?

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.

What does the asterisk (*) mean when used in the select clause?

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.

Why is intersect not working in MySQL?

Since MySQL does not provide support for the INTERSECT operator. However, we can use the INNER JOIN and IN clause to emulate this operator.


2 Answers

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 qualified tbl_name.* reference

    SELECT 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.

like image 117
John Flatness Avatar answered Oct 20 '22 01:10

John Flatness


but

select *, f from blegg 

will work fine.

Possibly an unqualified * has to appear as the first expression in the select?

like image 39
Steve Claridge Avatar answered Oct 20 '22 00:10

Steve Claridge