CODEWITHSUNDEEP
mysql
DutchRay
DutchRay

Reputation: 21

MySQL subquery quirk or expected behaviour?

I'm trying to gain more knowledge concerning the MySQL database and I'm hoping someone over here might be able to explain to me the following issue as I cant find much about this particular behavior anywhere:

This works:

SELECT justaname FROM (SELECT productName AS justaname FROM kclbs_products) sdfsdfsd

While this doesnt:

SELECT justaname FROM (SELECT productName AS justaname FROM kclbs_products)

This really puzzle's me and I believe it to be a quirk because whatever I turn the string 'sdfsdfsd' into doesn't matter, the query still works, even when its just a single character (or a very large sequence of characters for that matter). This 'issue' doesn't represent a problem to me currently but I would really like to know the 'why' behind it to be able to deal with this kinda behavior anywhere in the possible future should I ever have to.

Thanks in advance.

[UPDATE] Two users have helped providing me with the answer to my question, so its solved, and thanks!

Upvotes: 1

Views: 92

Answers (2)

Sajjad Pirahesh
Sajjad Pirahesh

Reputation: 136

This is not related to MySQL, and its a rule in all DBMS's. if you use subquery in FROM clause you must alias it.

When you write something after subquery (like sdfsdfsd), DBMS consider it as alias name of table derived from subquery.

You can see some information about subqueries in FROM clause in:

  1. https://dev.mysql.com/doc/refman/5.7/en/from-clause-subqueries.html

and

  1. http://www.geeksengine.com/database/subquery/subquery-in-from-clause.php
  2. it also explained in subquery in FROM must have an alias

Upvotes: 0

zr9
zr9

Reputation: 536

It subquery name

SELECT ... FROM (subquery) [AS] name ...

Here is reference from mysql docs
The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names.

For futher info use https://dev.mysql.com/doc/refman/5.7/en/from-clause-subqueries.html

Upvotes: 2

Related Questions