What is a nested subquery? in which order does sql evaluate nested subqueries?

Subqueries are nested when they appear in the WHERE clause of the parent statement. When Oracle Database evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:

• Uncorrelated IN subqueries

• IN and EXISTS correlated subqueries, as long as they do not contain aggregate functions or a GROUP BY clause

You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:

• You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or ROWNUM0 hint in the subquery.

  A SQL nested query, also known as a subquery or subselect, is a SELECT query method embedded within another query. It’s an efficient way to get the desired output that may require multiple query steps or to handle queries that are interdependent.

  When to use Nested Subqueries

  You can use nested subqueries in a couple different ways:

  • You can query over subqueries (e.g. selecting from a subquery)
  • You can replace 1D arrays (e.g. a typical list of items) and single field joins with a single subquery in the WHERE or HAVING clause

  In order to use a subquery, there are a couple of syntactical rules that need to be followed:

  • The subquery needs to be enclosed with parentheses
  • Depending on the SQL engine you are using, you might need to alias a given subquery
  • If using in a WHERE or HAVING clause the SELECT statement of an subquery can only return the single field being evaluated

  Example SQL interview question using a nested subquery

  Suppose you are given the following table showing company sales:

  Table: sales_info

  datesale_idsale_usd2020-01-051111936952020-01-0711128796172020-01-071113752878.........

  Calculate the cumulative percent of total sales on a given day. The output of the table should look like the example table below.

  datepct_total_sales2020-01-05X%2020-01-07Y%

  You can work through this example using the interactive SQL fiddle here.

  Before we start writing SQL, we'll break the question into steps:

  • Calculate the daily sales total
  • Calculate the cumulative sum of the daily sales total and total sales for all days
  • Divide the daily total sales by the cumulative sum

  1. Calculate the daily sales total

  First, we'll write the base query, which will become a subquery in the next step. The below query calculates the daily sales total and you can interact with the query with this SQL fiddle.

  2. Calculate the cumulative sum of the daily sales total and total sales for all days

  The query below calculates the cumulative sum of the daily sales total and total sales for all days. You can interact with the query below with this SQL fiddle.

  You'll notice in this step that we build a query around the previous query from the step above. In order to successfully build the subquery, we needed to enclose the subquery in parentheses and ensure that we alias the table (since we're using MySQL, note that this isn't a requirement for some SQL variants). We also need to ensure that we include all fields needed for the outer query (e.g. date and total sales).

  3. Divide the cumulative total sales by the cumulative sum

  The last step is to divide the cum_total by the total. We can accomplish this in the same step as above (just dividing the two windowing functions), or we can build a subquery over the previous step. The query below uses another subquery resulting in the end query having 2 nested subqueries. You can interact with the query below with this SQL fiddle.

  When you need to include in your WHERE clause selection criteria that only exists in another table, you can add subqueries to a SQL statement to retrieve the values that satisfy the condition. A subquery is a complete query that appears in the WHERE or HAVING clause of an SQL statement.

  You can specify up to 16 subqueries within a single SQL statement, and you can specify subqueries within a subquery. Subqueries run from last to first within the main SQL statement in which they appear.

  Rules for creating a subquery

  • Enclose the subquery in parentheses.
  • Specify only one column or expression in a subquery unless you are using IN, ANY, ALL, or EXISTS.
  • A subquery cannot contain a BETWEEN or LIKE clause.
  • A subquery cannot contain an ORDER BY clause.
  • A subquery in an UPDATE statement cannot retrieve data from the same table in which data is to be updated.
  • A subquery in a DELETE statement cannot retrieve data from the same table in which data is to be deleted.

  The following query displays the names and IDs of employees who work in Boston. The subquery (in parentheses) finds the department number for the location of BOSTON in the Q.ORG table. Then the main query selects the names of the employees in that department from the Q.STAFF table.

  SELECT NAME, ID
    FROM Q.STAFF
    WHERE DEPT=(SELECT DEPTNUMB
                FROM Q.ORG
                WHERE LOCATION='BOSTON')

  In the next example, the subquery and main query retrieve data from the same table. The subquery calculates the average salary for all the employees in the Q.STAFF table. Then, the main query selects the salespeople whose salaries are equal to or greater than the average salary.

  SELECT ID, NAME, SALARY
    FROM Q.STAFF
    WHERE JOB = 'SALES' AND
      SALARY >= (SELECT AVG(SALARY)
                 FROM Q.STAFF)

  Retrieving more than one value with a subquery

  Usually a subquery selects only one column and returns only one value to the query. However, you can create a subquery that uses the ANY or ALL keywords with comparison operators (=, ¬=, >, >=, <, or <=) to return a particular set of values. In addition, just as you use the IN keyword in place of multiple OR statements in a query, you can also use IN in place of the ANY keyword in a subquery.

  The query in the following figure selects the department, name, and salary of the employees who have the highest salary in their departments. The subquery calculates the maximum salary for each department that is selected by the main query. The correlation name, Y, compares each row that is selected by the query to the maximum salary that is calculated for the department in the subquery.

  What is a nested subquery?

  A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

  In which of the following subqueries can be nested?

  A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.

  What are the types of nested queries in SQL?

  The above example has three subqueries that are nested subquery, inner subquery, and outer subquery.

  What is the order of query execution in subqueries?

  The subquery (inner query) executes once before the main query (outer query) executes. The main query (outer query) use the subquery result.