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

From: Cimode <cimode_at_hotmail.com>
Date: 16 Sep 2006 14:48:10 -0700
Message-ID: <1158443290.181094.198800_at_e3g2000cwe.googlegroups.com>


Chris Lim wrote:
> Cimode wrote:
> > I know what you are getting at. But you are using the wrong example
> > and asking the wrong question. The impact of using NULL values have
> > nothing to do with presentation issues. You don't design data schemas
> > according to the way data should look on user's screen but according to
>
> Okay bad example. I wasn't really thinking about returning the data to
> the end-user, but from a programmer's view if I had to perform some
> calculations on data where I wanted flights with and without an actual
> departure date (and the calculation uses actual departure date if it's
> present and scheduled departure date if not). Without doing an outer
> join I can't see any way of doing this without having two queries or a
> UNION, which effectively doubles the amount of code to be written.
>
> Another example. Say you needed to identify all flights that had the
> same scheduled_departure_date and actual_departure_date as another
> flight (including flights with no actual_departure_date). If
> actual_departure_date is in a separate table, then wouldn't you need
> two queries (or a UNION) to do the same thing you would in a single
> query if you allowed NULLs?

Chris Lim wrote:
> Cimode wrote:
> > I know what you are getting at. But you are using the wrong example
> > and asking the wrong question. The impact of using NULL values have
> > nothing to do with presentation issues. You don't design data schemas
> > according to the way data should look on user's screen but according to
>
> Okay bad example. I wasn't really thinking about returning the data to
> the end-user, but from a programmer's view if I had to perform some
> calculations on data where I wanted flights with and without an actual
> departure date (and the calculation uses actual departure date if it's
> present and scheduled departure date if not). Without doing an outer
> join I can't see any way of doing this without having two queries or a
> UNION, which effectively doubles the amount of code to be written.

> Another example. Say you needed to identify all flights that had the
> same scheduled_departure_date and actual_departure_date as another
> flight (including flights with no actual_departure_date). If
> actual_departure_date is in a separate table, then wouldn't you need
> two queries (or a UNION) to do the same thing you would in a single
> query if you allowed NULLs?
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 be a second table for flights if you pretend your schema is normalized.  If 2 relations have the same attributes then they should be one relation. In a word, one can not state: I have a shitty non normalized schema design and I complain because it makes queries more difficult to write: in this precise cas, it is because the schema is not normalized that you have to go through such pain...  

> Thanks, I'll check that out.
>
> Cheers,
> Chris
Received on Sat Sep 16 2006 - 23:48:10 CEST

Original text of this message