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

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 majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>