where clause in nested isNull query giving unexpected result

Viewed 328

The below query returns around 200000 results.

The working of nested where clause in this query is not very clear i.e where is it coming in the picture ?

If I comment out the where clause inside the isNull then I get 0 results, which is fine and expected as the Max(invoiceID) is not null after join.

select * from CustomerServices where isNull((
                                SELECT MAX(invoiceid) 
                                FROM Invoices 
                                    LEFT JOIN InvoicesHistory
                                        ON InvoicesHistory.ServiceHistoryID = Invoices.ServiceHistoryID 
                                WHERE serviceID = Invoices.serviceID
                            ),0)=0

Please let me know if you want me to add more information.

1 Answers
Related