Re: New join type ?

Thanks for suggestions, Jan and Mikito !

To clarify what I was after, the typical statement might be (taking the advice about coalesce and the typo)

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)

and let the wildcard column value take care of the rest (yes, performing limited cartesian products in some cases). If there were a built-in wildcard value, and rules governing its use, this syntax could not only be handled, but also in a way which could be better understood by the query optimiser.

But I understand what you are saying, Jan, with the existing products it is actually just a case of working within the confines of the langauge and trying to get as friendly as possible with the optimiser.

<rant>
What I am really getting at in the end is that every line of code I write is a line that has to be maintained, and I really hate being forced to write unions (or ANYTHING long winded), when there could be a much shorter way of expressing the same thing. Of course, we can't expect query optimisers to be psychic, but it is exactly these sort of structures and rules which make the optimiser's job easier.

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. Surely, a few judicious extensions would give the parser more information to base its decisions on.
There are a number of problems with this in SQL at present (sorry, I'm a purist, I just can't help myself).
</rant>

Mikito :

No the Null value is not a type, I believe its correct name would be a system sentinal value (my misquote entirely). It applies across all types and hence this is really not a type discussion.

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. A lot of things can be done with CASE, but in the end it's a really inefficient option.
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.

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.

Benj Received on Thu Mar 28 2002 - 06:52:19 CET