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

Question

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.

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

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

Here's a simple fiddle on SqlFiddle (it keeps timing out for me, I hope you have better luck).

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.

Answer 1

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).

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.

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

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.