Contents
Can Subqueries use alias?
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. This is suitable when anybody wants to obtain information from two separate tables.
Are subqueries and nested queries the same?
A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used. A subquery must return only one column.
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. Because the subquery may be evaluated once for each row processed by the outer query, it can be slow.
Why we use correlated query?
Correlated subqueries are used for row-by-row processing. A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query.
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:
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:
Is there way to give a subquery an alias in Oracle 11g SQL?
The self join query looks something like: I don’t have an Oracle instance to test with, but what you posted should be valid ANSI-89 JOIN syntax. Here it is in ANSI-92: Your query should be fine. I wouldn’t be surprised if Oracle rewrote the queries so that the plan would be the same anyway.