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
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.
令我惊讶的是,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:
这是SQL标准还是MySQL专业?我可以确定此语法的结果实际上是来自同一(外部)表的列值吗?扩展版本将是这样的:
SELECT id, (SELECT name FROM t AS t1 where t1.id=t2.id) FROM t AS t2
but the EXPLAIN reports No tables used in the Extra column for the former version, which I think is very nice.
但是EXPLAIN报告在前版本的Extra列中没有使用表格,我觉得这非常好。
Here's a simple fiddle on SqlFiddle (it keeps timing out for me, I hope you have better luck).
这是SqlFiddle上的一个简单的小提琴(它为我保留时间,我希望你有更好的运气)。
Clarification: I know about subqueries, but I always wrote subqueries (correlated or not) that implied a table to select from, hence causing an additional step in the execution plan; my question is about this syntax and the result it gives, that in MySQL seems to return the expected value without any.
澄清:我知道子查询,但我总是编写子查询(相关或不相关),暗示了一个表可供选择,因此在执行计划中引起了额外的步骤;我的问题是关于这种语法及其给出的结果,在MySQL中似乎没有任何返回预期的值。
3 个解决方案
#1
2
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.
这是SQL语言的默认行为,它在SQL ANSI 2011上通过ISO / IEC 9075-1:2011(en)文档定义。不幸的是它没有公开。 4.11 SQL语句一节中描述了此行为。
This behavior happens because the databases process the select comand without the from clause, therefore if it encounters:
出现这种情况是因为数据库在没有from子句的情况下处理select命令,因此如果它遇到:
select id, (select name) from some
It will try to find that name field as a column of the outer queries to process.
它将尝试将该名称字段作为要处理的外部查询的列。
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 ANSI文档,该文档在FULL联机,但它是针对SQL ANSI 99的,并且该部分可能与新文档不同。我认为,没有检查,它是在4.30节附近。看一看。我真的推荐阅读(我在当天做了那个)。
Database Language SQL - ISO/IEC 9075-2:1999 (E)
数据库语言SQL - ISO / IEC 9075-2:1999(E)
#2
3
What you within your first query is a correlated subquery which simply returns the name column from the table t. no actual subquery needs to run here (which is what your EXPLAIN is telling you).
您在第一个查询中的内容是相关子查询,它只返回表t中的名称列。没有实际的子查询需要在这里运行(这是你的EXPLAIN告诉你的)。
In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.
在SQL数据库查询中,相关子查询(也称为同步子查询)是使用外部查询中的值的子查询(嵌套在另一个查询中的查询)。
SELECT id, (SELECT name) FROM t
is the same as
是相同的
SELECT id, (SELECT t.name) FROM t
Your 2nd query
你的第二个问题
SELECT id, (SELECT name FROM t AS t1 where t1.id=t2.id) FROM t AS t2
Also contains correlated subquery but this one is actually running a query on table t to find records where t1.id = t2.id.
还包含相关的子查询,但是这个实际上是在表t上运行查询以查找t1.id = t2.id的记录。
#3
0
It's not standard. In oracle,
这不标准。在甲骨文,
select 1, (select 2)
from dual
Throws error, ORA-00923: FROM keyword not found where expected
引发错误,ORA-00923:找不到FROM关键字
How can you be sure of your results? Get a better understanding of what the query is supposed to acheive before you write it. Even the exetended version in the question does not make any sense.
你怎么能确定你的结果?在编写查询之前,请更好地了解查询应该实现的内容。即使是问题中的解决版本也没有任何意义。
#1
2
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.
这是SQL语言的默认行为,它在SQL ANSI 2011上通过ISO / IEC 9075-1:2011(en)文档定义。不幸的是它没有公开。 4.11 SQL语句一节中描述了此行为。
This behavior happens because the databases process the select comand without the from clause, therefore if it encounters:
出现这种情况是因为数据库在没有from子句的情况下处理select命令,因此如果它遇到:
select id, (select name) from some
It will try to find that name field as a column of the outer queries to process.
它将尝试将该名称字段作为要处理的外部查询的列。
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 ANSI文档,该文档在FULL联机,但它是针对SQL ANSI 99的,并且该部分可能与新文档不同。我认为,没有检查,它是在4.30节附近。看一看。我真的推荐阅读(我在当天做了那个)。
Database Language SQL - ISO/IEC 9075-2:1999 (E)
数据库语言SQL - ISO / IEC 9075-2:1999(E)
#2
3
What you within your first query is a correlated subquery which simply returns the name column from the table t. no actual subquery needs to run here (which is what your EXPLAIN is telling you).
您在第一个查询中的内容是相关子查询,它只返回表t中的名称列。没有实际的子查询需要在这里运行(这是你的EXPLAIN告诉你的)。
In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.
在SQL数据库查询中,相关子查询(也称为同步子查询)是使用外部查询中的值的子查询(嵌套在另一个查询中的查询)。
SELECT id, (SELECT name) FROM t
is the same as
是相同的
SELECT id, (SELECT t.name) FROM t
Your 2nd query
你的第二个问题
SELECT id, (SELECT name FROM t AS t1 where t1.id=t2.id) FROM t AS t2
Also contains correlated subquery but this one is actually running a query on table t to find records where t1.id = t2.id.
还包含相关的子查询,但是这个实际上是在表t上运行查询以查找t1.id = t2.id的记录。
#3
0
It's not standard. In oracle,
这不标准。在甲骨文,
select 1, (select 2)
from dual
Throws error, ORA-00923: FROM keyword not found where expected
引发错误,ORA-00923:找不到FROM关键字
How can you be sure of your results? Get a better understanding of what the query is supposed to acheive before you write it. Even the exetended version in the question does not make any sense.
你怎么能确定你的结果?在编写查询之前,请更好地了解查询应该实现的内容。即使是问题中的解决版本也没有任何意义。