How to prevent CTE execution until reached OR NOT EXISTS

Question

I have an SQL Server query that uses some CTEs (Common Table Expressions). It has two tables that it selects the data from. The two tables are identical in structure, but not necessary data. The query will first select from table_a and if no rows are fetched, it then selects from table_b. The query is something like this:

;WITH cte_a AS (
  ...
), cte_b AS (
  ...
) 
SELECT * 
FROM table_a
INNER JOIN cte_a ON condition_a
OR NOT EXISTS (
  SELECT *
  FROM table_b
  INNER JOIN cte_b ON condition_b
)

The current problem that I have is that cte_b will always be executed regardless of whether table_a returns any rows. This is not very ideal for me; I would like to have cte_b execute if and only if the subquery for table_a returns no rows.

I tried moving the cte_b to be just before the subquery for table_b as

;WITH cte_a AS (
  ...
)
SELECT * 
FROM table_a
INNER JOIN cte_a ON condition_a
OR NOT EXISTS (
  ;WITH cte_b AS (
    ...
  ) 
  SELECT *
  FROM table_b
  INNER JOIN cte_b ON condition_b
)

However, the IDE complains. I think that this wasn't the way CTEs are supposed to be used.

You will need to use an SP and `IF` conditions. There is no way to **reliably** force query optimiser to execute (or not execute) parts of queries conditionally. — Alex, Nov 06 '19 at 05:52
What's more CTEs may even be executed multiple times, when they are referenced more than once. See: https://stackoverflow.com/a/706983/6305294 and https://stackoverflow.com/questions/11169550/is-there-a-performance-difference-between-cte-sub-query-temporary-table-or-ta — Alex, Nov 06 '19 at 06:04
@Alex Using `IF` conditions will mean that CTEs will not be used right? — krismath, Nov 06 '19 at 06:44

score 2 · Accepted Answer · answered Nov 06 '19 at 06:31

Create temporary table for storing the data - split the query to two separate INSERT statements, where the second is executed only in no data is populated after first query is completed. Something like this:

CREATE TABLE #TEmp
(

);


;WITH cte_a AS (
  ...
)
INSERT INTO #TEmp
SELECT * 
FROM table_a
INNER JOIN cte_a ON condition_a

IF NOT EXISTS(SELECT 1 FROM #TEmp)
BEGIN;

INSERT INTO #TEmp
SELECT * 
FROM table_a
WHERE NOT EXISTS (
  SELECT *
  FROM table_b
  INNER JOIN cte_b ON condition_b
)

score 1 · Answer 2 · edited Nov 06 '19 at 05:44

1

This is the nature of CTE's.

The best thing would be to not use a CTE and use a join, but I assume you can't.

I'd suggest you try using a sub-query so the engine can process it all at once.

edited Nov 06 '19 at 05:44

Dale K

25,246
15
42
71

answered Nov 06 '19 at 05:39

Watermelon

169
4

score 0 · Answer 3 · answered Nov 06 '19 at 05:35

0

I would phrase this using a union, and then excluding the second half of the union on cte_b should the first CTE have any results.

;WITH cte_a AS (
    ...
),
cte_b AS (
    ...
)

SELECT * 
FROM table_a
INNER JOIN cte_a ON condition_a
UNION ALL
SELECT *
FROM table_b
INNER JOIN cte_b ON condition_b
WHERE NOT EXISTS (
    SELECT 1
    FROM table_a
    INNER JOIN cte_a ON condition_a
)

answered Nov 06 '19 at 05:35

Tim Biegeleisen

502,043
27
286
360

`cte_b` is still executed in all cases, right? I want to execute `cte_b`, which can take a long time, only if there is no records from `table_a` subquery. – krismath Nov 06 '19 at 05:38
@krismath No, the `WHERE` clause containing the not exists clause would be executed _first_, and the second CTE would only run should the A CTE be empty. – Tim Biegeleisen Nov 06 '19 at 06:58

score 0 · Answer 4 · answered Nov 06 '19 at 05:45

;WITH    cte1 AS
        (
        ....
        ),
        cte2 AS
        (
        ...
        )

SELECT * 
FROM table_a
INNER JOIN cte_a ON condition_a ---Choose Your Condition...

UNION ALL

SELECT *
FROM table_b
INNER JOIN cte_b ON condition_b ---Choose Your Condtion....
WHERE NOT EXISTS (SELECT * FROM cte2)

Note:-

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.

How to prevent CTE execution until reached OR NOT EXISTS

4 Answers4