Re: CROSS JOIN
- From: "Tony Rogerson" <tonyrogerson@xxxxxxxxxxxxxxxx>
- Date: Tue, 17 Jan 2006 07:48:15 -0000
> It is possible, but we talked about that years ago and the concensus
> was that depreciating the original FROM .. WHERE syntax would never
> happen. Too much old code depends on it and WHERE is different from
> ON. You need both and woudl have to invent a totally different
> syntax.
It can happen, other things have been depreciated in the standard that have
a lot of old code depending on them; luckily i think most database vendors
leave the feature in and it became a properitary extension.
> The problem is that the WHERE clause is done after the FROM clause.
> This makes a honking big difference in OUTER JOINs:
Yes, but thats just outer join stuff. Where you put the filter clause does
not affect the INNER JOINs, only OUTER JOIN logic, if you put the filter on
the ON clause of the OUTER JOIN then the filter is applied before the main
WHERE clause; by putting the filter from a column on the OUTER table on the
WHERE clause the join almost always becomes an INNER JOIN.
> idea. It lets you put parens in the right places as you look at the
> code.
Why would you want to use braces? It only leads to confusion and they are of
little use out in the field; i don't think in 12 years of SQL programming
I've ever needed to use them except on the WHERE clause, which if you have
used the ANSI 92 syntax is a lot lot cleaner because it isn't cluttered by
join predicates.
> SELECT a, b, c
> FROM Foo, Bar, Gulk
> WHERE Foo.a BETWEEN Bar.b AND Gulk.c;
What you are actually doing there is a CROSS JOIN and then a filter, rather
than a direct join between the tables.
It is better written like this...
SELECT a, b, c
FROM Foo f
CROSS JOIN Bar b
CROSS JOIN Gulk g
WHERE f.a BETWEEN b.b AND g.c;
And here is a working example in Microsoft SQL Server...
declare @Foo table (
numb tinyint not null
)
insert @Foo values( 1 )
insert @Foo values( 2 )
insert @Foo values( 3 )
insert @Foo values( 4 )
insert @Foo values( 5 )
declare @Bar table (
numb tinyint not null
)
insert @Bar values( 2 )
insert @Bar values( 3 )
declare @Gulk table (
numb tinyint not null
)
insert @Gulk values( 2 )
insert @Gulk values( 3 )
SELECT *
FROM @Foo f, @Bar b, @Gulk g
WHERE f.numb BETWEEN b.numb AND g.numb
SELECT *
FROM @Foo f
CROSS JOIN @Bar b
CROSS JOIN @Gulk g
WHERE f.numb BETWEEN b.numb AND g.numb
In summary, you should use the most frequent standard and not mix standards,
you will come a cropper if something in the old standard is depreciated.
Mixing the joins in the WHERE clause makes it difficult to really understand
what the query is actually doing, I used to prefer the ANSI 89 myself and
took quite a while getting used to writing ANSI 92 and now I fully realise
the benefit of extrapolation the join clauses out of the filtering (where)
clause.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@xxxxxxxxxxxxx> wrote in message
news:1137455896.943751.19490@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>> There is nothing to say that it will be depreciated in a future ANSI
>>> specification. <<
>
> It is possible, but we talked about that years ago and the concensus
> was that depreciating the original FROM .. WHERE syntax would never
> happen. Too much old code depends on it and WHERE is different from
> ON. You need both and woudl have to invent a totally different
> syntax.
>
> The problem is that the WHERE clause is done after the FROM clause.
> This makes a honking big difference in OUTER JOINs:
>
> SELECT a, b, c
> FROM Foo LEFT OUTER Bar ON a = 42 AND b = 32;
>
> is not the same as:
>
> SELECT a, b, c
> FROM Foo LEFT OUTER JOIN Bar ON a = 42
> WHERE b = 32;
>
>>> It also makes sense when you start using outer joins, the mixed syntax
>>> is horrible imho and leads to more work when diagnosing performance
>>> problems / supporting the SQL <<
>
> You have no choice about the OUTER JOIN, OUTER UNION, etc. syntax, of
> course. And I agree that INNER JOIN with OUTER JOIN is usually a good
> idea. It lets you put parens in the right places as you look at the
> code.
>
> The only down side is that you start thinking in binary operators
> applied in a sequential order, just like you did with procedural code
> instead of more abstract concepts.
>
> But I prefer the old FROM.. WHERE syntax instead of a chain of INNER
> JOINs. The extra words crowd the page and make the code hard to read,
> as well as hiding relationships. Consider this simple search
> condition:
>
> SELECT a, b, c
> FROM Foo, Bar, Gulk
> WHERE Foo.a BETWEEN Bar.b AND Gulk.c;
>
> You can see the "between-ness" relationship. Now look at this and see
> that higher level declarative concept of "between-ness" is lost in the
> forest.
>
> SELECT a, b, c
> FROM (Foo
> INNER JOIN
> Bar
> ON Foo.a >= Bar.b)
> INNER JOIN
> Gulk
> ON Foo.a <= Gulk.c;
>
> Foo is at the top, but it is used at the bottom of the code block.
> This is not good when you are debugging code. The code you want to
> look at should be physically close together and not several lines a
> part
>
> Add a few parens in the wrong places, build a derived table with a bad
> column name and it can be a mess to debug.
>
.
- References:
- CROSS JOIN
- From: David Segall
- Re: CROSS JOIN
- From: Tony Rogerson
- Re: CROSS JOIN
- From: --CELKO--
- CROSS JOIN
- Prev by Date: Re: Is MySQL File Format Identical to SQL File Format?
- Next by Date: Re: natural join
- Previous by thread: Re: CROSS JOIN
- Next by thread: Re: CROSS JOIN
- Index(es):
Relevant Pages
|
