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

From: Chris Lim <blackcap80_at_hotmail.com>
Date: 16 Sep 2006 17:21:17 -0700
Message-ID: <1158452477.694390.312970@k70g2000cwa.googlegroups.com>

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.

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 Sat Sep 16 2006 - 19:21:17 CDT