Sql syntax: select without clause as a subquery in select (subselect)

Question

When editing some queries to add alternatives for columns without values, I accidentally wrote something like this (here is a simple version):

SELECT id, (SELECT name) FROM t 

To my surprise, MySQL did not return any errors, but completed a query giving my expected results (values ​​for the name column). I tried to find documentation about this, but without success.

Is this SQL standard or MySQL specialty?
Can I be sure that the result of this syntax is indeed a column value from the same (external) table? An extended version will look like this:

 SELECT id, (SELECT name FROM t AS t1 where t1.id=t2.id) FROM t AS t2 

but EXPLAIN reports No tables used in the Extra column for the first version, which I think is very nice.

Here's a simple fiddle on SqlFiddle (it keeps time for me, hope you are more lucky).

Clarification: I know about subqueries, but I always wrote subqueries (correlated or not), which implied a table for selection, therefore, causing an additional step in terms of execution; my question is about this syntax and the result it gives that in MySQL it seems to return the expected value without any.

Answer 1

This is the default behavior for the SQL language, and it is defined in SQL ANSI 2011 according to ISO / IEC 9075-1: 2011 (en) documentation. Unfortunately, it is not open. This behavior is described in Section 4.11 of the SQL-Statement.

This is because the databases process the select command without the from clause, so if it occurs:

 select id, (select name) from some 

He will try to find this name field as an external query column to process.

Fortunately, I remember that some time ago I answered someone here and found a valid accessible link to an ANSI SQL document that is on the network FULL, but for SQL ANSI 99 and the section may not be the same as the new document. I think I did not check that this is around section 4.30. Take a look. And I really recommend reading (I did it the same day).

SQL Database Language - ISO / IEC 9075-2: 1999 (E)

Answer 2

What you in your first query is a correlated subquery that just returns the name column from table t . the actual subquery should not start here (what your EXPLAIN tells you).

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 the values ​​from an external query.

https://en.wikipedia.org/wiki/Correlated_subquery

 SELECT id, (SELECT name) FROM t 

coincides with

 SELECT id, (SELECT t.name) FROM t 

---

Your second request

 SELECT id, (SELECT name FROM t AS t1 where t1.id=t2.id) FROM t AS t2 

It also contains a correlated subquery, but it actually runs the query in table t to find the entries where t1.id = t2.id.

Answer 3

This is not a standard. In oracle

 select 1, (select 2) from dual 

Throws an error, ORA-00923: FROM keyword not found where expected

How can you be sure of your results? Get a better idea of ​​what the request should get before writing. Even the extended version in the question makes no sense.