Take the 2-minute tour ×

What is the term for the select construct in the following select statement that is in bold?

SELECT a.t1 as a, (SELECT b.n as b FROM b WHERE b.x = a.t1), c.t2 as c FROM a,c WHERE a.x = c.x

I was explaining that this can be done in oracle but when asked what it was called, I couldn't think of any term. Is there a term for this? Or is it just selecting a select result?

EDIT: expanded query to make sub-query use clear

share|improve this question
Four answers inside of a minute, all calling it something slightly different. I love SQL... –  T.J. Crowder Sep 22 '10 at 13:33
It's clearly a subquery, but I think whether it's scalar as guigui42 suggests or corrolated as RedFilter suggests would depend on parts you've left out. At least, that's the terminology the MySQL folks use: dev.mysql.com/doc/refman/5.0/en/subqueries.html –  T.J. Crowder Sep 22 '10 at 13:44
@TJ: it could actually be both or neither, as well, although if it was not scalar, the query would bomb... –  RedFilter Sep 22 '10 at 13:54
@RedFilter: Good point! So assuming it's functional, it's either a scalar subquery, or a scalar corrolated subquery. –  T.J. Crowder Sep 22 '10 at 13:57
Also, this "correlated scalar subquery" can be easily replaced by an INNER JOIN most of the times, easier to read and more efficient ! –  guigui42 Sep 22 '10 at 14:29

4 Answers 4

up vote 14 down vote accepted

It is a subquery. If b.n refers to a table aliased as b in the outer query, then it could be referred to as a correlated subquery.

As guigui42 notes, it is also a scalar query, since it is returning at most only one column and row. In fact, you must take care to ensure at most only one row is ever returned, or the query may crash at some later date. This is often guarded against by using TOP 1 or equivalent.

share|improve this answer
"...because it is referring to a table in the outer query." It is? I'm missing it. It could be, that "..." after the WHERE leaves a big question mark... –  T.J. Crowder Sep 22 '10 at 13:38
@TJ: You are right, I was mistakenly assuming b.n referred to a table outside the query., which we can't tell from the segment posted. I will update my response. –  RedFilter Sep 22 '10 at 13:50
@RedFilter: Well, whether you're mistaken is unclear. You could well be right. It's a good answer regardless. –  T.J. Crowder Sep 22 '10 at 13:51
to be more precise, i think its a "scalar correlated subquery", since it returns one and only one result per line in the select clause. –  guigui42 Sep 22 '10 at 13:55
Looks like "scalar correlated subquery" might be the most accurate term –  sal Sep 22 '10 at 14:58

i would say "Scalar subquery"

EDIT : as RedFilter said, it is also a correlated subquery.

so it is a Scalar correlated subquery

share|improve this answer
Well, it may be corrolated. It may not, the OP didn't give us enough to work with. –  T.J. Crowder Sep 22 '10 at 13:59
How could it not be a correlated sub-query? The question (unamended) says "(SELECT b.n as b FROM n WHERE ...)"; the column b.n has to come from somewhere other than table 'n', doesn't it? (Unless there is a way for nesting structured types in a table, in which case table N might contain a compound column B which has a component N...does Oracle indulge in that stuff? Some of the ORDBMS do.) –  Jonathan Leffler Sep 22 '10 at 14:19
@Jonathan Leffler: there's no evidence in this case to suggest that a table in the main query's FROM clause will appear in the scalar subquery's WHERE clause. –  onedaywhen Sep 22 '10 at 15:07
@onedaywhen: what is the b in b.n? –  Jonathan Leffler Sep 22 '10 at 15:52
@onedaywhen: also, a table from the main query can appear (and appears to appear) in the select-list; that would make it a correlated query, whether the table is mentioned in the WHERE or not. –  Jonathan Leffler Sep 22 '10 at 16:17

A nested sub-query.

...notoriously poor performers if mis-used (which is quite often) as well.

share|improve this answer
Just as am aside, this is being used to replace oracle case statements to replace ID codes with descriptive terms. Its more readable and performance is acceptable. –  sal Sep 22 '10 at 15:06

This is referred to as a sub-select.

share|improve this answer
Lots of things are sub-selects. I need something more specific. –  sal Sep 22 '10 at 15:01

Your Answer


