Oracle 1Z0-071, Subqueries

What is subquery in SQL?

A subquery is simply a SQL query nested inside a larger query.

You may also hear or see subqueries referred to as inner queries or inner selects, and the outer query also referred to as the parent, outer query or outer select.

How the SQL Engine works

Be aware that the inner query executes before the outer parent query. This means that the results of the inner query are available to the parent.

Performance Note:

Subqueries with our without scalars always add overhead, more or less, depends on what is being requested. An important factor is if the scalar function is not deterministic. A deterministic function will always return the same result upon supplied the same parameters.

In general, anything that ‘SELECT’ cannot be deterministic as data may change. If it is deterministic then delays are limited to difference of supplied values.

For SELECT subqueries in overall it is better to try to join instead of using subquery at select clause. In most cases a join is feasible.

Where to use a Subquery

A subquery may occur in in a SELECT, FROM, OR WHERE CLAUSE. Additionally, a subquery can be nested in a SELECT, INSERT, UPDATE, or DELETE statement.

In most cases a subquery will be added within a WHERE clause of another SELECT statement.

In evaluating subqueries, you can use comparison operators such as >, <, = against single row subqueries or comparison operators such as IN, ANY or ALL in the case of multi-row subqueries.

Types of Subqueries

Scalar subquery is a query that returns one row consisting of one column. These are very common. A scalar method is used for aggregate functions such as max, min, sum, avg and so on. Whenever you want to return a single value after execute the query.

Correlated subqueries are dependent queries where the parent query provides a value in controlling the result set of the subquery. These are often used to control the rows returned in a select. Correlated subqueries can be used anywhere a scalar can be used. Most subqueries will fall into this type.

Hierarchical subquery is a subquery that returns hierarchical data. Think of a company reporting structure, a hierarchical query would return a data set representing the CEO down to the bottom reporting employee while keep the relationships of each layer represented.

Purpose of a Subquery

A subquery can be used to control the rows that are inserted into a table, or to define conditions applied to a SELECT statement within the WHERE clause of that statement. A Scalar subquery, a query that returns a single value, can be used to provide values inside other statements.