i.wormsbecker at uleth.ca
Sun Jan 19 21:42:29 EST 2003
On January 19, 2003 07:59 pm, Rod Giffin wrote:
> On Sun, 2003-01-19 at 17:29, B McKee wrote:
> > Thanks guys for the good info - When I hit a head scratcher like that I
> You're on the right track Brian. But I've got something else for you to
> consider - especially if you're using this in a program. I don't want
> this to get too complicated so I won't go into the details of what can
> occur unless you want me to, or if you run into trouble with it.
> In the example you gave, you had a sponsor name with a value of NULL.
> That does not necessarily mean what you expected - and could result in
> some logic errors in the translation between SQL and your program. You
> are going to have to trap the value coming from the sponsor name field,
> and test it for the content of "NULL" (record 0 was selected), or a
> value of NULL (an error occurred... nothing was returned.)
Is this neccessarily the case? I am not sure how mysql works, but I beleive
that you can't test an element of a tuple for NULL directly. If you compare a
tuple element that is NULL with NULL, the comparison will be false always.
Which is why you can usually use operators such as IS NULL or NOT NULL etc in
your SQL statements.
For instance, the query SELECT * FROM table WHERE a_column = NULL; will always
return 0 rows regardless of how many tuples have NULL for a_column.
> See, in a program (depending on the language), you can initialize a
> variable to null. The variable exists, but doesn't contain anything.
> But in SQL, that is represented by "", by the character '', by the
> integer 0, or the float 0.00. (depending on what the field type is). In
> SQL, null means NOTHING like that exists. If a result set is null, it
> means that no records that match the query were returned. If a field
> returns a null, it does not mean the name is empty - it means it doesn't
> exist. The difference is subtle, but important to grasp.
I am confused as to what you are saying here. In SQL, a NULL integer is just
NULL. A NULL float is NULL. Also, an empty string "" is not NULL, it is an
It seems as though you are trying to explain how you can determine if a value
is NULL in a tuple from within a program, for instance a perl script. I am
not familiar with perl so cannot comment on that, but I should hope that NULL
is not 0.00 as 0.00 is a valid float value.
Some other examples of problems that NULL can cause is when you have a column
that contains an integer value and can be NULL. You want to run an SQL query
to find the average of all the columns. So you run something such as SELECT
sum(a_column) / count(a_column); How do you add NULL to your total? What kind
of behavior do you get? Does the NULL value count towards the total number of
elements you have added? These are all difficult things that you have to deal
with when using NULL.
In the case the OP had, I would suggest using an emptry string to signify that
the user did not enter data for that field rather than NULL.
> If I just confused you, for a more concise explanation of how it works
There was also a discussion on one of the postgresql lists. Here is the link:
More information about the OCLUG