Execution Mechanisms of Derived Tables and Subqueries in SQL Server: A Comparative Analysis of INNER JOIN and APPLY

Core Principles of SQL Query Execution Mechanisms

In SQL Server database systems, the query optimizer is responsible for transforming user-declared SQL statements into efficient execution plans. Understanding this transformation process is crucial for writing high-performance queries. Derived tables, as a form of subquery, often cause confusion among developers regarding their execution behavior.

Execution Behavior of INNER JOIN with Derived Tables

Consider the following example of an INNER JOIN query using a derived table:

SELECT E.EID, DT.Salary FROM Employees E
INNER JOIN
(
    SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
    FROM SalaryRate SR
    INNER JOIN AttendanceDetails AD ON AD.EID = SR.EID
) DT
ON DT.EID = E.EID

Many developers mistakenly believe that the derived table is fully executed and materialized before being joined with the main table. In reality, the SQL Server optimizer typically rewrites the query into an equivalent expanded form:

SELECT E.EID, SR.Rate * AD.DaysAttended AS Salary
FROM Employees E
INNER JOIN SalaryRate SR ON E.EID = SR.EID
INNER JOIN AttendanceDetails AD ON AD.EID = SR.EID

This rewriting enables the optimizer to apply broader optimization strategies, including predicate pushdown and join order optimization.

Execution Characteristics of APPLY Operators

APPLY operators (including CROSS APPLY and OUTER APPLY) are commonly used for correlated subquery scenarios:

SELECT E.EID, DT.Salary FROM Employees E
CROSS APPLY
(
    SELECT (SR.Rate * AD.DaysAttended) Salary
    FROM SalaryRate SR
    INNER JOIN AttendanceDetails AD ON AD.EID = SR.EID
    WHERE SR.EID = E.EID
) DT

The traditional view suggests that APPLY causes the subquery to execute once for each row of the outer table. However, modern SQL Server optimizers can recognize patterns and may rewrite APPLY as JOIN operations, particularly when the subquery does not depend on columns from the outer table.

Execution Plan Analysis and Performance Considerations

By analyzing actual execution plans, one can observe the decision-making process of the optimizer. Creating sample tables in a test environment:

CREATE TABLE #Employees (EID INT NOT NULL);
INSERT #Employees VALUES (0);
CREATE TABLE #SalaryRate (EID INT NOT NULL, Rate MONEY NOT NULL);
CREATE TABLE #AttendanceDetails (EID INT NOT NULL, DaysAttended INT NOT NULL);

When comparing execution plans of different query variants, it becomes evident that INNER JOIN and CROSS APPLY may generate identical execution plans when logically equivalent. The optimizer selects the most appropriate join algorithm based on statistics, index availability, and data distribution:

• Nested Loop Join: Suitable for small datasets or when effective indexes exist
• Hash Match Join: Appropriate for medium-sized datasets
• Merge Join: Optimal for large, pre-sorted datasets

Predicate Pushdown and Query Optimization

The SQL Server optimizer can push filtering conditions from outer queries into derived tables, avoiding unnecessary full table scans. For example:

SELECT E.EID, DT.Salary FROM #Employees E
LEFT JOIN
(
    SELECT SR.EID, (SR.Rate * AD.DaysAttended) Salary
    FROM #SalaryRate SR
    INNER JOIN #AttendanceDetails AD ON AD.EID = SR.EID
) DT
ON DT.EID = E.EID
WHERE E.EID = 1

In this query, the condition E.EID = 1 is pushed down to the scan operation on the #AttendanceDetails table, significantly reducing I/O overhead.

Practical Development Recommendations

1. Avoid rigid assumptions about execution mechanisms: SQL is a declarative language, and the optimizer has the authority to choose the best execution path
2. Prefer standard JOIN syntax: Unless specific correlated subquery functionality is needed, standard JOIN is generally easier to optimize
3. Analyze execution plans: Use SET SHOWPLAN_XML ON or SSMS graphical execution plan tools
4. Monitor I/O statistics: Understand query resource consumption through SET STATISTICS IO ON
5. Maintain updated statistics: Ensure the optimizer has accurate data distribution information

Conclusion

The intelligence of the SQL Server query optimizer is often underestimated. The execution methods of derived tables and APPLY operators are not fixed but dynamically determined by the optimizer based on specific contexts. Developers should focus on query logical correctness and readability, leaving performance optimization to the optimizer while supporting it through appropriate index design and statistics maintenance. When performance issues arise, systematic execution plan analysis proves more effective than syntax-based assumptions.