1

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;
O. Jones
  • 103,626
  • 17
  • 118
  • 172
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • 1
    possible duplicate of [Differences between "IS NULL" and "ISNULL()" in Mysql](http://stackoverflow.com/questions/3530124/differences-between-is-null-and-isnull-in-mysql) – John Woo Feb 19 '13 at 13:34
  • 1
    I would say that `IS NOT NULL` is easier to read. – Ja͢ck Feb 19 '13 at 13:40

1 Answers1

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks. Do you make your recommendation because of "the query optimizer"? What is this query optimizer and how does it apply? – user1032531 Feb 19 '13 at 13:43