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

"D. Dante Lorenso" <dante(at)lorenso(dot)com> writes:
> Here's what I'm doing, tell me if I'm crazy:

> The column I'm comparing to is 'folder_id'. The folder_id column is a
> foreign key to a folder table. If folder_id is NULL, the row is not in
> a folder.

Yup, you're crazy. The best interpretation of NULL according to the SQL
spec is that you don't know which folder the row is in.

If you are willing to reserve ID 0 as not being any real folder, then
folder_id = 0 would be a reasonable way to represent "it's not in a
folder". This is positive knowledge, entirely distinct from "I don't
know if it's in a folder, much less which one".

Now there is a small problem with that, which is that if you want to
have folder_id be a foreign key to a table of folders then it doesn't
work so well. But do not let yourself be tempted to use NULL as a
solution to that. What I'd suggest after a few seconds' thought is that
you create an explicit "unclassified" folder and put every "not in a
folder" row into the "unclassified" folder.

regards, tom lane