Understanding the Visibility of External Tables in CROSS APPLY

Introduction

The CROSS APPLY operator is a powerful tool in SQL Server that allows you to perform an operation on each row of one table against another table. In this article, we will delve into the nuances of using external tables with CROSS APPLY. We will explore how visibility of these external tables affects query execution and provide guidance on when to use CROSS APPLY versus other techniques.

Background

For those unfamiliar with SQL Server, CROSS APPLY is a syntax for joining two or more tables. When used in combination with table-valued functions, it can be particularly useful for performing operations on large datasets.

The question presented at the beginning of this article appears to stem from a common gotcha when working with CROSS APPLY. The user encountered an issue where the external table’s value was not visible within the subquery. This behavior seems counterintuitive, as one might expect the value to be available for use in the subquery.

Recursive Queries and External Tables

The example provided in the question highlights a specific scenario involving recursive queries and external tables. In this case, we have three CTEs (table_1, table_parents, and an alias table):

WITH table_1 AS (
  SELECT 1 col_id FROM dual UNION ALL
  SELECT 2 col_id FROM dual UNION ALL
  SELECT 4 col_id FROM dual
),
table_parents AS (
  SELECT 1 col_id, 3 parent_id, 'manager' parent_type FROM dual UNION ALL
  SELECT 2 col_id, 3 parent_id, 'manager' parent_type FROM dual UNION ALL
  SELECT 3 col_id, 4 parent_id, 'manager' parent_type FROM dual
)

The user attempts to use CROSS APPLY against the alias table (table_parents) with the following query:

SELECT *
  FROM table_1 t1
  CROSS APPLY (SELECT parent_id
                 FROM (select col_id, parent_id FROM table_parents WHERE parent_type = 'manager') pars
                WHERE connect_by_isleaf = 1
                START WITH pars.col_id = t1.col_id
              CONNECT BY NOCYCLE PRIOR pars.parent_id = pars.col_id) uptimate_parent;

However, the query fails with an error message [42000][904] ORA-00904: "T1"."COL_ID": invalid identifier.

The Issue

The root cause of this issue lies in how SQL Server handles table-valued functions (TVFs) and CROSS APPLY. When using CROSS APPLY, the subquery is executed once for each row in the outer table. In this case, the connect_by_isleaf = 1 clause is used to traverse the recursion.

The problem arises when SQL Server attempts to access columns from the outer table (table_1) within the subquery. In this example, the column name col_id is not visible in the subquery context because it was aliased during the initial CTE execution. This means that any reference to t1.col_id within the subquery results in an invalid identifier error.

Moving the CROSS APPLY Subquery to the SELECT List

As suggested by the question, moving the CROSS APPLY subquery directly to the SELECT list resolves the issue:

WITH table_1 AS (
  SELECT 1 col_id FROM dual UNION ALL
  SELECT 2 col_id FROM dual UNION ALL
  SELECT 4 col_id FROM dual
),
table_parents AS (
  SELECT 1 col_id, 3 parent_id, 'manager' parent_type FROM dual UNION ALL
  SELECT 2 col_id, 3 parent_id, 'manager' parent_type FROM dual UNION ALL
  SELECT 3 col_id, 4 parent_id, 'manager' parent_type FROM dual
)
SELECT t1.col_id
     , (SELECT parent_id
          FROM table_parents pars
         WHERE connect_by_isleaf = 1
         START WITH pars.col_id = t1.col_id) uptimate_parent
  FROM table_1 t1;

In this revised query, the column references are resolved at runtime by the subquery itself. This change eliminates any issues with aliasing and access to the outer table’s columns.

Alternative Approaches: Join or CTE

While CROSS APPLY can be an effective tool for certain operations, there may be situations where other techniques are more suitable:

• Join: If you require the same level of recursion as before but need additional flexibility in your join logic, consider using a regular join instead of CROSS APPLY. For example:

SELECT t1.col_id , (SELECT parent_id FROM table_parents pars WHERE connect_by_isleaf = 1 AND pars.col_id = t1.col_id) uptimate_parent FROM table_1 t1;

*   **CTE**: If you need to perform additional operations or transformations on the data, consider using a CTE instead of `CROSS APPLY`. For example:

    ```markdown
WITH transformed_parents AS (
  SELECT col_id, parent_id, 'manager' parent_type
    FROM table_parents WHERE parent_type = 'manager'
),
recursive_query AS (
  SELECT t1.col_id, (SELECT parent_id FROM transformed_parents pars WHERE connect_by_isleaf = 1 AND pars.col_id = t1.col_id) uptimate_parent
  FROM table_1 t1
)
SELECT *
  FROM recursive_query;

By understanding how CROSS APPLY interacts with external tables and leveraging other SQL Server features, you can improve the performance and maintainability of your queries.

Conclusion

The provided question highlights a specific scenario where using CROSS APPLY against an external table leads to visibility issues. By breaking down the problem step-by-step and exploring alternative approaches, we gained insight into how SQL Server handles TVFs and recursion.

When deciding between CROSS APPLY, joins, or CTEs for recursive queries, consider your specific use case and the requirements of each technique. With a deeper understanding of these subtleties, you can write more efficient and effective SQL Server code that effectively leverages the capabilities of the database system.

Last modified on 2024-04-08