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

From: Chris Lim <blackcap80_at_hotmail.com>
Date: 19 Sep 2006 02:28:41 -0700
Message-ID: <1158658121.790662.202730_at_b28g2000cwb.googlegroups.com>

Chris Lim wrote:

> SELECT FirstName,


>              LastName,


>              ISNULL(MiddleName, '') AS MiddleName,


Bad example of ISNULL since it's not required in this case!

A simpler example:

SELECT CustomerID,

             FirstName
              + ISNULL(' ' + MiddleName, '')
              + ' ' + LastName  AS FullName

FROM Customers

If you don't have a problem with outer joins then it's not too bad, but you still have to deal with NULLs:

SELECT C.CustomerID,

              C.FirstName
              + ISNULL(' ' + CM.MiddleName, '')
              + ' ' + C.LastName  AS FullName

FROM Customers C
LEFT OUTER JOIN CustomerMiddleName CM

   ON CM.CustomerID = C.CustomerID

Otherwise it requires a UNION:

SELECT C.CustomerID,

              C.FirstName
              + ' ' + C.LastName  AS FullName

FROM Customers C
WHERE NOT EXISTS(

             SELECT *
             FROM CustomerMiddleName CM
             WHERE CM.CustomerID = C.CustomerID
              )

UNION SELECT C.CustomerID,

              C.FirstName
              + ' ' + CM.MiddleName
              + ' ' + C.LastName  AS FullName

FROM Customers C
INNER JOIN CustomerMiddleName CM

   ON CM.CustomerID = C.CustomerID

Chris Received on Tue Sep 19 2006 - 11:28:41 CEST