Re: 3 value logic. Why is SQL so special?

Chris Lim wrote:
> Cimode wrote:
> > You should not be surprised. SQL is not a relational language for a
> > long time. SQL is by nature a redundant language. Additionally, your
> > second example is a bad one two. At the first place, there should not
>
> I also wanted to confirm something regarding the number of extra tables
> that would need to be created to avoid NULLs. Am I right in saying that
> if I have a Customer table which has a lot of optional attributes (that
> a customer may or may not have or supply (e.g. middle name, birth date,
> gender, number of children, ethnicity, etc)), then I would need a
> separate table for each of those attributes (e.g. CustomerMiddleName,
> CustomerBirthDate etc) to avoid NULLs? That seems like an awful lot of
> overhead to me.
Depends what you call *awful lot of overhead*. If *awful lot of overhead* is creating 3 extra tables to do it the right way ONCE at design time then I may call you a lazy ass. If *awful lot of overhead* is all the redundant code and conditions (including NULLS, excluding NULLS) you and all the developpers will have to add to make your queries return correct count results ( during the entire system life cycle) then you will find out quicly the *awful lot of overhead* really happens only when NULLS are allowed not the opposite!

If you wan to find out more about NULLS and their impact, you will need to do some reading. My time is as limited as yours, therefore I can not answer all questions...(especially if you don't post your table definitions) ;)

> And if I needed a query to identify customers with the same last name +
> middle name + birth_date + gender, what would the query look like for
> that? If they were NULLable columns in the same table, it would be
> something like:
>
> SELECT last_name,
> ISNULL(middle_name, '') AS middle_name,
> ISNULL(birth_date, '1 Jan 2050') AS birth_date,
> ISNULL(gender, '') AS gender,
> COUNT(*) AS cnt
> FROM Customer
> GROUP BY last_name,
> ISNULL(middle_name, ''),
> ISNULL(birth_date, '1 Jan 2050'),
> ISNULL(gender, '')
> HAVING COUNT(*) > 1
>
> What would be the equivalent in the no NULLs database?
>
> Chris
Received on Sun Sep 17 2006 - 20:24:26 CEST