CODEWITHSUNDEEP
sql-servert-sql
jawogo
jawogo

Reputation: 37

Conditional outer apply based on result of different outer apply

I have a query which needs to retrieve data from multiple tables. For two of these tables, the data is retrieved by an outer apply, as it is necessary to query a time range for the data. 

SELECT a.DATA, b1.DATA, c1.DATA
FROM TABLE_A a 
OUTER APPLY 
    (SELECT TOP 1 b.[DATA]
     FROM TABLE_B b
     WHERE b.TIME_START <= a.TIME_START 
    ) AS b1
OUTER APPLY 
    (SELECT TOP 1 c.[DATA]
     FROM TABLE_C c
     WHERE c.TIME_START <= a.TIME_START 
       AND c.TIME_STOP >= a.TIME_START 
    ) AS c1

My problem is that TABLE_C is a very big table and querying this table takes some time, but I would only need to query this table if the result from TABLE_B is null. TABLE_B is rather small compared to TABLE_C.

For example, this is a valid result of the query:

   [a.DATA] [b.DATA] [c.DATA]
1  VALUE_A  VALUE_2  NULL
2  VALUE_B  NULL     VALUE_3

Is there a way to make the outer apply to TABLE_C conditional to the result of TABLE_B for each row, as in the above example to only execute for line 2?

Upvotes: 3

Views: 2272

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Something like this:

SELECT  a.DATA ,
        b1.DATA ,
        c1.DATA
FROM    TABLE_A a
        OUTER APPLY ( SELECT TOP 1
                                b.[DATA]
                      FROM      TABLE_B b
                      WHERE     b.TIME_START <= a.TIME_START
                    ) AS b1
        OUTER APPLY ( SELECT    CASE WHEN b1.[DATA] IS NULL
                                     THEN ( SELECT TOP 1
                                                    c.[DATA]
                                            FROM    TABLE_C c
                                            WHERE   c.TIME_START <= a.TIME_START
                                                    AND c.TIME_STOP >= a.TIME_START
                                          )
                                     ELSE NULL
                                END AS DATA
                    ) AS c1

Or:

   SELECT  a.DATA ,
            b1.DATA ,
            c1.DATA
    FROM    TABLE_A a
            OUTER APPLY ( SELECT TOP 1
                                    b.[DATA]
                          FROM      TABLE_B b
                          WHERE     b.TIME_START <= a.TIME_START
                        ) AS b1
            OUTER APPLY ( SELECT TOP 1
                                    c.[DATA]
                          FROM      TABLE_C c
                          WHERE     c.TIME_START <= a.TIME_START
                                    AND c.TIME_STOP >= a.TIME_START AND b1.[DATA] IS NULL
                        ) AS c1

Upvotes: 2

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

SELECT a.DATA, b1.DATA, c1.DATA
FROM TABLE_A a 
OUTER APPLY 
    (SELECT TOP 1 b.[DATA]
     FROM TABLE_B b
     WHERE b.TIME_START <= a.TIME_START 
    ) AS b1
OUTER APPLY 
    (SELECT TOP 1 c.[DATA]
     FROM TABLE_C c
     WHERE c.TIME_START <= a.TIME_START 
       AND c.TIME_STOP >= a.TIME_START 
       AND b1.data IS NULL
    ) AS c1

But I do not know what optimizer will do in that case. Will it take all data from C and after that remove all the records with B. However you can also try to duplicate the query:

SELECT a.DATA, b1.DATA, c1.DATA
FROM TABLE_A a 
OUTER APPLY 
    (SELECT TOP 1 b.[DATA]
     FROM TABLE_B b
     WHERE b.TIME_START <= a.TIME_START 
    ) AS b1
OUTER APPLY 
    (SELECT TOP 1 c.[DATA]
     FROM TABLE_C c
     WHERE c.TIME_START <= a.TIME_START 
       AND c.TIME_STOP >= a.TIME_START 
       AND NOT EXISTS (
       SELECT * FROM TABLE_B b WHERE  b.TIME_START <= a.TIME_START
       )
    ) AS c1

Upvotes: 1

Related Questions