Filter OUTER APPLY column in WHERE clause

Question

I currently have a complex SQL query which is inserted into a temp table. The query includes an OUTER APPLY as not all returned records will apply to the result set.

I also need to use the OUTER APPLY columns in the WHERE clause to filter results but also include the results which do not apply into the OUTER APPLY .e. All Outer APPLY results = 1 and non-outer apply results.

This is a simple version of the query layout:

INSERT INTO #temp (X, Y, Z, O1, O2)
SELECT 
    X Y Z
FROM T1 
INNER JOIN T2, T etc.
OUTER APPLY (
    SELECT O1, O2 FROM XYZ…) OATable
WHERE
    OATable.O1 = 1 -- I tried just adding “IN (1, NULL)” but this 
still excludes the results. 

Any help would be greatly appreciated.

Platform: SQL Server 2012+

Thank you

Answer 1

You can't compare directly to NULL because nothing equates to NULL (not even NULL itself). That precludes using IN here. Instead, just use an OR statement:

INSERT INTO #temp (X, Y, Z, O1, O2)
SELECT
    X, Y, Z,
FROM T1 
INNER JOIN T2 ON ...
OUTER APPLY (SELECT O1, O2 FROM XYZ…) OATable
WHERE
    OATable.O1 = 1 OR OATable.O1 IS NULL

That assumes that O1 is a NOT NULL column in XYZ.