WHERE X NOT IN filter error?

[anna_k_hd]

Having consulted documentation on IN operators here I still cannot make out why the following returns NULL and no WORK_ORDER_NO records return TRUE:

SELECT work_order_no FROM `pr-edw-views-thd.ORD_WORKORDER.WO_HDR` a
WHERE work_order_no NOT IN
(SELECT DISTINCT work_order_no  FROM`pr-edw-views-thd.ORD_WORKORDER.WO_HDR` WHERE STATUS_DESCRIPTION = 'Work Order Cancelled')​

While the follow returns many records with logic which should be the same:

SELECT work_order_no FROM `pr-edw-views-thd.ORD_WORKORDER.WO_HDR` a
LEFT JOIN
(SELECT DISTINCT work_order_no  FROM`pr-edw-views-thd.ORD_WORKORDER.WO_HDR` WHERE STATUS_DESCRIPTION = 'Work Order Cancelled')b
USING(work_order_no)
WHERE b.work_order_no IS NULL

Is my understanding wrong that the output should be the same? If not than why is the output of the first query NULL?

[Joevanie · Accepted solution]

The NOT IN operator returns NULL if any value in the list being compared to is NULL. To fix this issue, you can add an additional condition to the subquery to exclude NULL values like this:

SELECT work_order_no 
FROM `pr-edw-views-thd.ORD_WORKORDER.WO_HDR` a
WHERE work_order_no NOT IN
(SELECT DISTINCT work_order_no  
 FROM `pr-edw-views-thd.ORD_WORKORDER.WO_HDR` 
 WHERE STATUS_DESCRIPTION = 'Work Order Cancelled' 
 AND work_order_no IS NOT NULL)