ISNULL() verse IS NULL performance when using LEFT JOINs

Question

sub1 and sub2 both have a 1-to-1 relationship with super.

I wish to determine whether a join exists for either one of them for a given super record.

The following two queries should produce my desired results. Are there any reasons to use !ISNULL() versus IS NOT NULL?

SELECT super.*
FROM super
LEFT OUTER JOIN sub1 ON super.id=sub1.super_id
LEFT OUTER JOIN sub2 ON super.id=sub2.super_id
WHERE (!ISNULL(sub1.id) OR !ISNULL(sub2.id)) AND super.id=123;

SELECT super.*
FROM super
LEFT OUTER JOIN sub1 ON super.id=sub1.super_id
LEFT OUTER JOIN sub2 ON super.id=sub2.super_id
WHERE (sub1.id IS NOT NULL OR sub2.id IS NOT NULL) AND super.id=123;

Answer 1

Use your second choice (IS NOT NULL). The query optimizer may or may not be able to help with the efficiency of your second query. But the query optimizer doesn't do functions. It assumes that it has to evaluate any function you give for all possible rows and columns; it doesn't try to infer the functions' meaning.