Why MySQL error using table alias on a simple select statement?

Question

I'm using phpmyadmin to test out some MySQL queries. I'm trying to write a larger, nested query, which is failing due to an unrecognized table alias, so I'm trying to debug smaller parts of it. However, I'm getting confusing errors when I try to use table aliases sometimes.

Can you explain why some of these queries throw errors?

SELECT * FROM table1 AS tablealias1 (works)

SELECT * FROM table1 GROUP BY userid (works)

SELECT * FROM table1 GROUP BY userid AS tablealias1 (error: #1064 - 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 'AS tablealias1 LIMIT 0, 25' at line 1 )

SELECT * FROM table1 WHERE userid=1 (works)

SELECT * FROM table1 WHERE userid=1 AS tablealias1 (same error as above)

(SELECT * FROM table1 WHERE userid=1) AS tablealias1 (same error as above)

You can't assign aliases to where or group by clauses. That's pretty basic. — John Conde
– John Conde, Commented Aug 6, 2014 at 15:03
as statements define column and table aliases. It is allowed only in the select clause (for a column alias) and the from clause (for a table alias). That is how SQL syntax works. — Gordon Linoff
– Gordon Linoff, Commented Aug 6, 2014 at 15:04
when you evaluate WHERE userid = 1 AS tablealias1 - WHAT do you expect to be saved in tablealias1 ? true? false? — Daniel W.
– Daniel W., Commented Aug 6, 2014 at 15:05
Try this: Select * FROM (SELECT * FROM table1 WHERE userid=1) AS tablealias1 — Ray
– Ray, Commented Aug 6, 2014 at 15:07

Ray · Accepted Answer · 2014-08-06 15:09:04Z

2

You alias things to:

rename the column display's name
give it a reference name for later use elsewhere in the query statement (whether you use it explicitly or implicitly--as long as it could be used elsehere)

If you're not doing either, an alias makes no sense. You can't alias a result set unless it's used inside a subquery, then you need an alias to reference it.

This will work:

 Select * FROM (SELECT * FROM table1 WHERE userid=1) AS tablealias1

as it implies

  Select tablealias1.* FROM (SELECT * FROM table1 WHERE userid=1) AS tablealias1

Alone, this is garbage:

  (SELECT * FROM table1 WHERE userid=1) AS tablealias1

answered Aug 6, 2014 at 15:09

Ray

41.6k22 gold badges110 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shimizu Over a year ago

Thanks, I didn't realize you couldn't give an alias to the result set ONLY unless it's inside a subquery. Even though making an alias for the result set of a single SELECT statement doesn't make practical sense, I didn't think it was invalid syntax. I was just doing these to try to debug a larger, nested query.

Collectives™ on Stack Overflow

Why MySQL error using table alias on a simple select statement?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related