10

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.

watery
  • 5,026
  • 9
  • 52
  • 92
  • I checked and it also works in SQLite and MSSQL so it's probably not only MySQL speciality. – Matt Dec 17 '15 at 16:54
  • Interesting to see that a sub query can be written even to select columns. I am also curious to know if we can select 2 or more columns like this ?? – Kanike Vamshi Krishna Dec 17 '15 at 16:56
  • @Kanike Vamshi Krishna what he has there is a subquery so it's only going to let him return 1 column. watery; If you look up correlated subquery you will find some good information on your experience there. – KorreyD Dec 17 '15 at 16:59
  • Yes i am very interested. I will try something new and post my updates under this question if i find anything different – Kanike Vamshi Krishna Dec 17 '15 at 17:07
  • 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)](https://www.iso.org/obp/ui/#iso:std:iso-iec:9075:-1:ed-4:v1:en) documentation. Unfortunately it is not open. This behavior is described on the section 4.11 SQL-Statements. This behavior happens because the databases process the select comand without the from clause, therefore if it encounters a `select id, (select name) from some` it will try to find that name field as a column of the outer queries to process. – Jorge Campos Dec 17 '15 at 17:16
  • Answering Kanike, no it isn't possible to select two columns as part of a select statement. I'm not adding this as an answer since I can't link the proprietary text as documentation. – Jorge Campos Dec 17 '15 at 17:16
  • @JorgeCampos Actually, that's the information I was looking for. To me, your comment is the correct answer to this question. – watery Dec 18 '15 at 12:05
  • @watery I've added as an answer and with a full document of a previoslly ansi sql version. :) – Jorge Campos Dec 18 '15 at 12:36

3 Answers3

4

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.

KorreyD
  • 1,274
  • 8
  • 15
  • I know about subqueries, and all the examples in the page you linked always imply a `FROM` clause; although I do recognize that the part you quoted gives an hint about the behaviour I'm experiencing. I've edited my question for clarity. – watery Dec 18 '15 at 11:56
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.

This behavior happens because the databases process the select comand without the from clause, therefore if it encounters:

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

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

Community
  • 1
  • 1
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
0

It's not standard. In oracle,

select 1, (select 2) 
from dual

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

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • "Oracle rejects it" is not very compelling evidence that something is not standard. I agree with your last sentence, though. I can only hope/assume that that is not the *real* query. – ruakh Dec 18 '15 at 05:39
  • By *Can I be sure about the result I'm getting?* (now removed) I actually meant I hadn't found any documentation which could confirm that the syntax is really reading the column value from the same table. The extended version is what I would have usually written to obtain the same result. I've edited, I' hope it's more clear. – watery Dec 18 '15 at 12:02