what is this SQL select construct called?

Question

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

Answer 1

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.

Answer 2

A nested sub-query.

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

Answer 3

This is referred to as a sub-select.

Answer 4

i would say "Scalar subquery"

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

so it is a Scalar correlated subquery