10-12-2012, 06:14 PM
Nested Queries and Performance Issues in SQL ITM 354
Nested Queries.doc (Size: 61.5 KB / Downloads: 344)
Introduction
Nested Queries are queries that contain another complete SELECT statements nested within it, that is, in the WHERE clause. The nested SELECT statement is called an “inner query” or an “inner SELECT.” The main query is called “outer SELECT” or “outer query.” Many nested queries are equivalent to a simple query using JOIN operation. The use of nested query in this case is to avoid explicit coding of JOIN which is a very expensive database operation and to improve query performance. However, in many cases, the use of nested queries is necessary and cannot be replaced by a JOIN operation.
Performance considerations:
The nested queries in this example involves simpler and faster operations. Each subquery will be executed once and then a simple select operation will be performed. On the other hands, the operations using join require Cartesian products of three tables and have to evaluate 2 join conditions and 2 selection conditions. Nested queries in this example also save internal temporary memory space for holding Cartesian join results.
Computer Procedures:
Conceptually, think of this query as stepping through EMPLOYEE table one row at a time, and then executing the inner query each time. The first row has A.fname = “John” and A.sex = “M” so that the inner query becomes SELECT Essn FROM dependent where essn = 12345678, dependent_name = “John” and sex = “M”; The first run of the subquery returns nothing so it continues to proceed to the next tuple and executes the inner query again with the values of A.SSN, A.fname and A.sex for the second row, and so on for all rows of EMPLOYEE.
The term correlated subquery is used because its value depends on a variable (or variables) that receives its value from an outer query (e.g., A.SSN, A.fname, A.sex in this example; they are called correlation variables.). A correlated subquery thus cannot be evaluated once and for all. It must be evaluated repeatedly -- once for each value of the variable received from the outer query. This is different from non-correlated subqueries explained below.