问题描述
在编辑一些查询以添加无值列的替代项时，我不小心写了这样的东西(这是简单版本):
While editing some queries to add alternatives for columns without values, I accidentally wrote something like this (here is the simplyfied version):
SELECT id, (SELECT name) FROM t
令我惊讶的是，MySQL没有引发任何错误，但是完成了查询并给出了我的预期结果(name列值).我试图找到有关它的任何文档，但没有成功.
To my surprise, MySQL didn't throw any error, but completed the query giving my expected results (the name column values).I tried to find any documentation about it, but with no success.
这是SQL标准还是MySQL专业知识?
我可以确定这种语法的结果确实是同一张(外部)表中的列值吗?扩展版本将如下所示:
Is this SQL standard or a MySQL specialty?
Can I be sure that the result of this syntax is really the column value from the same (outer) table? The extended version would be like this:
SELECT id, (SELECT name FROM t AS t1 where t1.id=t2.id) FROM t AS t2
，但是EXPLAIN在以前版本的Extra列中报告No tables used，我认为这非常好.
but the EXPLAIN reports No tables used in the Extra column for the former version, which I think is very nice.
这是一个简单的在SqlFiddle上进行提琴操作(我希望它可以让我不断超时你的运气更好).
Here's a simple fiddle on SqlFiddle (it keeps timing out for me, I hope you have better luck).
说明:我知道子查询，但我总是写子查询(无论是否相关)，这些子查询隐含了一个可供选择的表，因此在执行计划中增加了一个步骤.我的问题是关于这种语法及其给出的结果，在MySQL中似乎没有任何返回预期的值.
推荐答案
这是SQL语言的默认行为，它是在基于ISO/IEC 9075-1:2011(zh-CN)的SQL ANSI 2011 文档.不幸的是它没有打开.在4.11 SQL语句部分中描述了此行为.
This is the default behavior for the SQL language and it is defined on the SQL ANSI 2011 over ISO/IEC 9075-1:2011(en) documentation. Unfortunately it is not open. This behavior is described on the section 4.11 SQL-Statements.
之所以会发生这种现象，是因为数据库处理的是没有from子句的select comand，因此如果遇到以下情况:
This behavior happens because the databases process the select comand without the from clause, therefore if it encounters:
select id, (select name) from some
它将尝试查找name字段作为要处理的外部查询的列.
It will try to find that name field as a column of the outer queries to process.
幸运的是，我记得前一段时间我已经已回答有人在这里找到有效的指向SQL ANSI文档的有效链接，该文档完全在线，但适用于SQL ANSI 99，该部分可能与新文档不同.我认为，没有检查，它是在4.30节附近.看一看.我真的很推荐阅读(我那天回过头来做.)
Fortunately I remember that some while ago I've answered someone here and find a valid available link to an SQL ANSI document that is online in FULL but it is for the SQL ANSI 99 and the section may not be the same one as the new document. I think, did not check, that it is around the section 4.30. Take a look. And I really recommend the reading (I did that back in the day).
数据库语言SQL-ISO/IEC 9075-2:1999(E)
这篇关于SQL语法:不带from子句的select作为select(subselect)中的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！