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

From: Cimode <cimode_at_hotmail.com>
Date: 17 Sep 2006 11:16:34 -0700
Message-ID: <1158516994.069951.45080_at_b28g2000cwb.googlegroups.com>


Chris Lim wrote:
> Cimode wrote:
> > 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.
>
> No I didn't mean a separate table - I meant within the same table. For
> example, in SQL Server the query to find the occurrences of flights
> with the same scheduled and actual date (if the table allowed NULLs)
> would be:
>
> SELECT scheduled_date,
> ISNULL(actual_date, '1 Jan 1900') AS actual_date,
> COUNT(*) AS cnt
> FROM flights
> GROUP BY scheduled_date, ISNULL(actual_date, '1 Jan 19000')
> HAVING COUNT(*) > 1
>
> I'm just wondering what the equivalent would be if you did not allow
> NULLs. Wouldn't it be something like:
>
> SELECT scheduled_date,
> CONVERT(DATETIME, '1 Jan 1900') AS actual_date,
> COUNT(*) AS cnt
> FROM flights
> WHERE NOT EXISTS( SELECT *
> FROM flight_actuals fa
> WHERE fa.FlightID = f.FlightID)
> GROUP BY scheduled_date
> HAVING COUNT(*) > 1
>
> UNION
>
> SELECT f.scheduled_date,
> fa.actual_date,
> COUNT(*) AS cnt
> FROM flights f
> INNER JOIN flight_actuals fa
> ON fa.flightID = f.flightID
> GROUP BY f.scheduled_date, fa.actual_date
> HAVING COUNT(*) > 1
>
> Or is there a simpler way?

You can not have a table containing NULLS and say it is correctly designed, therefore dealing with NULLS imposes creating extra tables (in SQL at least)

Consider 2 types of FLIGHTS some scheduled having a departure and arrival time.
Some flights are scheduled and have a IATA_Flight_number others are unplanned.

Consider the two following SQL implementations, one having NULLS and the other not

  1. FLIGHT: flight_departure_time, flight_arrival_time, flight_IATA_number(allowing NULL)

in this design ALL flight_IATA_number would be NULL for all unplanned flights

2)
FLIGHT: flight_departure_time, flight_arrival_time SCHEDULED: flight_departure_time, flight_arrival_time, flight_IATA_number
UNPLANNED: flight_departure_time, flight_arrival_time

Using first method, to count ONLY scheduled flights require this

select count(*) from FLIGHT where flight_IATA_number is not NULL

while the second method allows

select count(*) from SCHEDULED

Does not that say it all? The simple fact of allowing NULLS imposes to you to do an index scan and a count while all it should do is a count.

:
> Chris
Received on Sun Sep 17 2006 - 20:16:34 CEST

Original text of this message