Re: New join type ?

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 28 Mar 2002 13:39:50 -0800
Message-ID: <bdf69bdf.0203281339.60b3d613_at_posting.google.com>


ConnectMMBen_at_netscape.net (Ben McIntyre) wrote in message news:<4a177e17.0203272152.827360_at_posting.google.com>...
> SELECT table1.*, table2.*
> FROM table1, table2
> WHERE (table1.col1 = table2.col1)
> AND (table1.wildcol = table2.wildcol OR table1.wildcol = 0 OR
> table1.wildcol IS NULL)
> where the first join is the 'normal' join and the second is a
> 'wildcard' join
>
> I'd love to just say
>
>
> SELECT table1.*, table2.*
> FROM table1, table2
> WHERE (table1.col1 = table2.col1)
> AND (table1.wildcol = table2.wildcol)
 

Since your first query is assymmetric, you need some syntax that would make the second one to appear assymetric as well.  

> I don't think the fact alone that something CAN be expressed in SQL is
> a good reason for putting up with having to write SQL in inefficient
> ways to get the engine to process it properly.

Agreed.

> One of the things I am also getting at is the difference between
> aggregate and row-wise operations.
> For example, the coalesce operator i used is a good example of a
> row-wise operator, which must be evaluated specifically for each row.

I don't follow here.

> A lot of things can be done with CASE, but in the end it's a really
> inefficient option.

It depends.

Anyway, combining together NULL and 0 as in your definiton doesn't look very appealing. Is it really necessary, or you can change 0 to some other value? Would you allow having two values - 0 and 1 together with NULL? No values at all - NULL only?

> The SUM or AVG operators are aggregates, which provide a much more
> efficient way of dealing with information. Because NULL is dealt with
> by the same engine at the same level as the join operation, its
> behaviour is in some ways similar to an aggregate funtion.

I'm hopelessly lost here.

> It is building this efficiency into the SQL parser which I am talking
> about.
>
> As I said, theoretical, speculative, but I couldn't help wondering if
> anyone had had the same idea.

In general, it is easy to come up with "new" types of join. For example,

select A.*, B.*
from A, B
where (exists col1, col2: A.col1=B.col2)

I doubt of its practicality, though. You seem to have a suggestion influenced by practical experience, so I'm listening. Received on Thu Mar 28 2002 - 22:39:50 CET

Original text of this message