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

From: Marshall <marshall.spight_at_gmail.com>
Date: 16 Sep 2006 12:38:14 -0700
Message-ID: <1158435494.020816.217810_at_m7g2000cwm.googlegroups.com>


Marshall wrote:
> David Portas wrote:
> > Cimode wrote:
> > > A UNION equivalent an LEFT OUTER JOIN BULLSHIT!!!. A LEFT OUTER JOIN is
> > > just SQL miserable way to force a presentation inspired perspective
> > > into design issues that should be regulated by normalization to
> > > preserve integrity. The proof is that you can do a UNION using 2
> > > relations but using LEFT OUTER JOIN when dealing with relations has no
> > > meaning. Consider 2 tables table1(id1, key1) and table2(id2, key2)
> > >
> > > Table1
> > > 1, A
> > > 2, B
> > > 3, C
> > >
> > > Table2
> > > 8, A
> > > 10, C
> > >
> >
> > T1{k,i1,i2} UNION (T1{k,i1} JOIN T2{k,i2})
> >
> > Obviously some suitable value or expression has to be supplied for i2
> > on the left hand side of the UNION.
>
> Isn't that going to run in to a problem if we infer a key for the
> result? With the left outer join, we can infer k as a key of
> the result, but with the above union we can't. (The key
> is {k,i1,i2}.) And we'll have more than one row for some
> values of k.
>
> Wouldn't it be more like:
>
> (T1{k,i1,i2} MINUS (T1{k} JOIN T2{k}))
> UNION (T1{k,i1} JOIN T2{k,i2})
>
> (Not sure if I got the syntax right.) I still don't see
> how to do key inference correctly on the above, but
> at least it won't have multiple rows for some k values.

Okay, here it is:

((T1{k} MINUS T2{k}) JOIN { (i2=<default>) }) UNION T2) JOIN T1 and you can infer k as the key of the result. Whew!

(I used <default> to represent whatever value you want to put in in place of where SQL would put a NULL.)

That's twice today I've replied to my own post. So here's a cat with a box on his head:

http://video.google.com/videoplay?docid=8135777110359469253

Marshall Received on Sat Sep 16 2006 - 21:38:14 CEST

Original text of this message