Contents
Can we use alias in subquery?
SQL Correlated Subqueries are used to select data from a table referenced in the outer query. The subquery is known as a correlated because the subquery is related to the outer query. In this type of queries, a table alias (also called a correlation name) must be used to specify which table reference is to be used.
The approach of the correlated subquery is bit different than normal subqueries.In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.
Why do subqueries have the same aliases as the parent query?
On a correlated subquery you have access to the parent’s aliases, so the aliases must be unique across the parent query and correlated subquery. If we take a correlated subquery such as the one below we have a single, global name space shared between the parent query and the correlated subquery:
How are correlated subqueries different from plain subquery?
Unlike a plain subquery, a correlated subquery is a subquery that uses the values from the outer query. Also, a correlated subquery may be evaluated once for each row selected by the outer query.
SELECT PR.PROVINCE_NAME ,CO.COUNTRY_NAME FROM PROVINCE PR JOIN ( SELECT COUNTRY_ID, COUNTRY_NAME FROM COUNTRY WHERE COUNTRY_ID=PR.COUNTRY_ID ) CO ON CO.COUNTRY_ID=PR.COUNTRY_ID WHERE PR.PROVINCE_ID IN (1,2) This is typed in, so I hope I haven’t introduced any syntax issues here.
Is there name space for aliases on nested subquery?
The name space for aliases on a nested subquery is separate from the name space on the parent. For example the query below has a nested subquery b that also has an alias b used within it. This would be potentially confusing to the programmer but fine with the DBMS engine: