Geek Answers Handbook

Why can mySQL resolve these column aliases when it is usually not possible to reuse an alias?

Most SQL experts will say that you cannot reuse an alias in a selection at the same level; often used for this CTE; or one wraps the request as a subquery so that an alias can be referenced. However, mySQL seems to allow this situation if the alias is referenced in a subquery inside select itself; so it’s not technically on the same level.

DEMO:

SELECT CONCAT(a, b) AS c1, CONCAT((SELECT c1), 2)
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1;

SELECT 1 a, 2 b, (SELECT A+B) c
     , concat((SELECT a),(SELECT b)) d 
     , greatest((SELECT a),(SELECT b), (SELECT c))

Both of the above queries work .. yes; they work. (or do a really good job to make it look like it works)

So far this is not so: as one would expect.

SELECT CONCAT(a, b) AS c1, CONCAT(c1, 2)
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1;

So the question here is twofold:

  • "mySQL" - , ?

  • , ; , , ?

: MySQL

+6
2

( ).

, . MySQL (select c1). c1 , .

SQL from. MySQL , , .

, MySQL, , . ( MySQL, "" "".) :

SELECT CONCAT((SELECT c1), 2), CONCAT(a, b) AS c1
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1;

:

'c1' ( )

, MySQL "" .

. ANSI . . :

SELECT CONCAT(a, b) AS c1, CONCAT((SELECT c1), 2)
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1 CROSS JOIN
     (SELECT 'abcdef' as c1) x;

c1 ? . , c1 .

+3

[9 2015 15:35] ...

( WHERE), :

1. ?. , MySQL SQL. - , , , . WHERE, SELECT.

2. SQL ? ( ?) SQL, MySQL . , ORDER BY.

3. , , , ? , , SELECT. , .

:

WHERE ( GROUP BY, ), ( ), SELECT, . 5.6 :

: create table t1 (a int, b int),

SELECT :

  select a+b as c,c+1 from t1;

1054 (42S22): 'c' ' '

c :

  select a+b as c,(select c+1) from t1;

:

  select (select c+1),a+b as c from t1;

1247 (42S22): 'c' ( )

, , SELECT ad-hoc. , , . WHERE .

:

  • , , mySQL. , . , ( 3 , ).
  • , , , . CTE , , .
  • " " " SELECT", -, , . "" ( ?)
+1

More articles:


All Articles