CODEWITHSUNDEEP
mysqlsqlselectalias
CheeseS
CheeseS

Reputation: 21

Is there any specific rules concerning about mysql alias?

Recently, I was learning MySQL following this particular guide. https://www.mysqltutorial.org/mysql-join/

Databases and tables were set up as directed in the tutorial. While running the following code, it results in a syntax error

SELECT 
    m.member_id, 
    m.name member, 
    c.committee_id, 
    c.name committee
FROM
    members m
INNER JOIN committees c 
	ON c.name = m.name;

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 ', 
    c.committee_id, 
    c.name committee
FROM
    members m
INNER JOIN commi' at line 3

With a few experiments, this syntax error can be fixed by changing the alias member to something else or by adding quotes around it. However, I am unclear to how and why these solutions work. PS: The version of MySQL I am using is Ver 8.0.19.

Upvotes: 1

Views: 115

Answers (2)

nbk
nbk

Reputation: 49375

member is a reserved word, so you must encapsulate the mmmber with single quotes

SELECT 
    m.member_id, 
    m.name 'member', 
    c.committee_id, 
    c.name committee
FROM
    members m
INNER JOIN committees c 
    ON c.name = m.name;

check also When to use single quotes, double quotes, and backticks in MySQL

Upvotes: 1

GMB
GMB

Reputation: 222482

member is a reserved word on some specific MySQL versions: it became reserved in version 8.0.17, then it seems like MySQL designers changed their mind and reversed that in version 8.0.19.

So you need to either change the alias name (which I would recommend), or surround it with backticks: this turns it to a quoted identifier, which never clash with reserved words, as explained in the documentation:

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.

And, further:

The identifier quote character is the backtick (`)

Upvotes: 1

Related Questions