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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>