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.