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

From: Chris Lim <blackcap80_at_hotmail.com>
Date: 19 Sep 2006 02:00:58 -0700
Message-ID: <1158656458.843298.310190_at_m73g2000cwd.googlegroups.com>

Roy Hann wrote:
> No doubt--because you are doing at run-time what could have been done at
> design time. How is your life made simpler by ISNULL?

For example, in the duplicate-checking process I mentioned earlier, there might be some code such as:

SELECT FirstName,

             LastName,
             ISNULL(MiddleName, '') AS MiddleName,
             COUNT(*) AS Cnt

FROM Customers
GROUP BY

             FirstName,
             LastName,
             ISNULL(MiddleName, '')
HAVING
             COUNT(*) > 1

This single query covers both customers with and without a MiddleName. If I had a separate CustomerMiddleName table, I would need to do a UNION to achieve the same thing (or a LEFT OUTER JOIN, but apparently these are bad too - what is your opinion on this?):

SELECT C.FirstName,

             C.LastName,
             '' AS MiddleName,
             COUNT(*) AS Cnt

FROM Customers C
WHERE

      NOT EXISTS(SELECT *
                         FROM CustomerMiddleName CM
                         WHERE CM.CustomerID = C.CustomerID)
GROUP BY
            C.FirstName,
            C.LastName

UNION SELECT C.FirstName,

             C.LastName,
             CM.MiddleName,
             COUNT(*) AS Cnt

FROM Customers C
INNER JOIN CustomerMiddleName CM

   ON CM.CustomerID = C.CustomerID
GROUP BY

             FirstName,
             LastName,
             MiddleName
HAVING
             COUNT(*) > 1

> The compexity doesn't go away. Any additional tables merely reveal the
> complexity that is otherwise hidden, lying in wait but still very much
> there. If you don't resolve it with a complex query you have to resolve it
> with complex code somewhere else. As horrid as it is, SQL is still almost

Isn't it better to hide complexity then, assuming that you end up with the correct result? Like you say, self-discipline keeps us out of trouble.

Chris Received on Tue Sep 19 2006 - 11:00:58 CEST