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

From: Chris Lim <blackcap80_at_hotmail.com>
Date: 18 Sep 2006 22:52:15 -0700
Message-ID: <1158645135.423153.18660_at_e3g2000cwe.googlegroups.com>

Bob Badour wrote:
> NULL confuses reasonably intelligent people because it breaks important > identities those people have been conditioned to expect such as:

Just to confirm that I understand the method to avoid NULLs, let's take this example:

CREATE TABLE Customers(

    CustomerID      INT         NOT NULL,
    FirstName       VARCHAR(30) NOT NULL,
    LastName        VARCHAR(30) NOT NULL,
    StatusCode      CHAR(1)     NOT NULL,
    MiddleName      VARCHAR(30) NULL,
    BirthDate       DATETIME    NULL,
    OccupationCode  CHAR(5)     NULL,
    EthnicityCode   CHAR(5)     NULL,
    ReligionCode    CHAR(5)     NULL

    PRIMARY KEY (CustomerID)
);

SELECT CustomerID,

        FirstName,
        LastName,
        MiddleName,
        BirthDate,
        OccupationCode,
        EthnicityCode,
        ReligionCode

FROM Customers;

Okay, we do not have any of the problems associated with NULLs here, but my point is not the problems with NULLs (as you say they are well-documented), but the overhead inccurred when avoiding NULLs. Am I correct that this would be the way to avoid NULLs?

CREATE TABLE Customers(

    CustomerID      INT         NOT NULL,
    FirstName       VARCHAR(30) NOT NULL,
    LastName        VARCHAR(30) NOT NULL,
    StatusCode      CHAR(1)     NOT NULL,

    PRIMARY KEY (CustomerID)
);

CREATE TABLE CustomerMiddleName(

    CustomerID      INT         NOT NULL,
    MiddleName      VARCHAR(30) NOT NULL,

    PRIMARY KEY (CustomerID)
);

CREATE TABLE CustomerBirthDate(

    CustomerID      INT         NOT NULL,
    BirthDate       DATETIME    NOT NULL,

    PRIMARY KEY (CustomerID)
);

CREATE TABLE CustomerOccupation(

    CustomerID      INT         NOT NULL,
    OccupationCode  CHAR(5)     NOT NULL,

    PRIMARY KEY (CustomerID)
);

CREATE TABLE CustomerEthnicity(

    CustomerID      INT         NOT NULL,
    EthnicityCode   CHAR(5)     NOT NULL,

    PRIMARY KEY (CustomerID)
);

CREATE TABLE CustomerReligion(

    CustomerID      INT         NOT NULL,
    ReligionCode    CHAR(5)     NOT NULL

    PRIMARY KEY (CustomerID)
);

SELECT CustomerID,

        FirstName,
        LastName,
        MiddleName,
        BirthDate = (SELECT BirthDate
                          FROM CustomerBirthDate c
                          WHERE c.CustomerID = Customers.CustomerID),
        OccupationCode = (SELECT
                             OccupationCode
                             FROM CustomerOccupation c
                             WHERE c.CustomerID =
Customers.CustomerID),
        EthnicityCode = (SELECT EthnicityCode
                               FROM CustomerEthnicity c
                              WHERE c.CustomerID =
Customers.CustomerID),
        ReligionCode = (SELECT ReligionCode
                               FROM CustomerReligion c
                                WHERE c.CustomerID =

Customers.CustomerID)
FROM Customers;

If there is an easier way in SQL to do this (without using outer joins, which I understand is also a no-no), let me know. Otherwise here we have a trivial table and query which has a reasonable amount of overhead added to it by avoiding NULLs. Imagine a more complicated query.

Chris Received on Tue Sep 19 2006 - 07:52:15 CEST