Re: [GENERAL] Need efficient way to do comparison with NULL as an option

[Joshua D. Drake]

D. Dante Lorenso wrote:

But if I want to find all the items which are not in any folder, I want:

  SELECT *
  FROM mytable
  WHERE folder_id IS NULL;

I don't have any folder_id 0, so on a URL I might do this:

  http://xyz/page.php?fid=123
  http://xyz/page.php?fid=0

Why not just have fid undef? thus you can just say:

If folder_id is 0, I do the NULL comparison.

  SELECT *
  FROM mytable
  WHERE folder_id IS NOT DISTINCT FROM NULLIF(?, 0);

SELECT *
   FROM mytable
   WHERE folder IS NULL;

Or have your PHP say...

if ($fid == 0) {
  $fid == "IS NULL"
}

(my php is extremely rusty but I think you get the idea)

And then build out your where clause.

That seems to do what I want. Is it bad design? Something I'm missing about indexing a NULL or something like that?

Sincerely,

Joshua D. Drake

-- Dante


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly