为何仅修改子查询字段别名的两条SQL语句一报错一正常？

第一条SQL及错误信息 #

mysql> SELECT 'STATUS', 
              status 
       FROM (SELECT ujm.user_id AS userId, 
                    ujm.job_id AS jobId, 
                    ujm.job_run_name AS jobRunName, 
                    j.response_payload -> '$.isSuccessful' AS isSuccessful, 
                    j.response_payload -> '$.error' AS errors, 
                    j.response_payload -> '$.destinationDetails' AS destinationDetails, 
                    s.name AS statusType 
             FROM user_job_mappings ujm 
             JOIN jobs j ON ujm.job_id = j.id AND j.is_deleted = 0 
             JOIN status_types s ON j.status_type_id = s.id AND s.is_deleted = 0 
             WHERE ujm.user_id = 24301 
             AND ujm.is_deleted = 0 
             order by ujm.job_id limit 1 offset 0
             ) jobs_data\G

ERROR 1054 (42S22): Unknown column 'status' in 'field list'

第二条SQL及执行结果 #

mysql>  SELECT 'STATUS', 
                status 
        FROM ( SELECT ujm.user_id AS userId, 
                      ujm.job_id AS jobId, 
                      ujm.job_run_name AS jobRunName, 
                      j.response_payload -> '$.isSuccessful' AS isSuccessful, 
                      j.response_payload -> '$.error' AS error, 
                      j.response_payload -> '$.destinationDetails' AS destinationDetails, 
                      s.name AS status 
                FROM user_job_mappings ujm 
                JOIN jobs j ON ujm.job_id = j.id AND j.is_deleted = 0 
                JOIN status_types s ON j.status_type_id = s.id AND s.is_deleted = 0 
                WHERE ujm.user_id = 24301 
                AND ujm.is_deleted = 0 limit 1 offset 0
               ) jobs_data\G
*************************** 1. row ***************************
STATUS: STATUS
status: SUCCESSFUL
1 row in set (0.00 sec)

原因说明 #

核心逻辑很简单：外层查询只能引用子查询返回的列别名，而非原表列名或自定义名称

• 第一条SQL的子查询中，将s.name的别名定义为statusType，这意味着子查询返回的结果集里只有statusType列，不存在status列。外层查询强行引用status，自然会触发"未知列"错误。
• 第二条SQL的子查询把s.name的别名改成了status，子查询返回的结果集里就包含了status列，外层查询引用这个别名就能正常匹配到对应数据。

总结：外层查询的列名必须和子查询中定义的别名完全一致，别名不匹配就会导致列找不到的错误。

内容的提问来源于stack exchange，提问作者Dhiraj Gilda