Cross Apply yields nulls

Question

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?

Answer 1

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).