CODEWITHSUNDEEP
sql-servert-sqlcross-apply
George Menoutis
George Menoutis

Reputation: 7240

Cross Apply yields nulls

I'm quoting MS:

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function.

This would mean that it would not return rows with null values, right? However, my query is:

select ....,cat_custom,....
from ...(various inner joins)...                
cross apply
                (
                    select  
                        case 
                            when i.cat1='01' then 1
                            when i.cat2='04' then 2
                            when i.cat2='07' then 3
                            when i.cat2 in ('08') or i.cat3 in ('014','847') then 4
                            else null 
                        end as cat_custom
                ) as cat_custom_query

...and sure enough, I get rows with nulls. Wouldn't that be OUTER apply's job? What is going on?

Upvotes: 2

Views: 1239

Answers (1)

gotqn
gotqn

Reputation: 43636

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function.

In your example, a row is produced - row, which is returning a NULL value.

You can try this:

select ....,cat_custom,....
from ...(various inner joins)...                
cross apply
                (
                    select  
                        case 
                            when i.cat1='01' then 1
                            when i.cat2='04' then 2
                            when i.cat2='07' then 3
                            when i.cat2 in ('08') or i.cat3 in ('014','847') then 4
                            else null 
                        end as cat_custom
                    WHERE i.cat1 IN ('01', '04', '07', '08', '014', '847')
                ) as cat_custom_query

Also, if this is part of your real query, you can add the result column in the SELECT statement. You do not need to use CROSS APPLY here, as you are not referring any SQL objects (tables, views, functions ,etc).

Upvotes: 4

Related Questions