Re: CROSS JOIN

>> 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.

.

Relevant Pages

  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)
  • Re: Difference in Left Join, Right Join
    ... RIGHT syntax exist. ... If you write SQL such that what you feel is the driving table is at ... The above is Oracle outer join syntax, ...
    (comp.databases.oracle.misc)
  • Re: SQLServer/Oracle Views
    ... >Mike John ... I suddenly remembered something Joe Celko ... It's not a syntax difference of the old outer-join syntax, ... LEFT OUTER JOIN TableB AS b ...
    (microsoft.public.sqlserver.programming)
  • Re: Not Common both Tables
    ... For exemple, I'm sure that the Full Outer Join is not available under ... I'm pretty sure that the Exitsstatement and the UNION ... For the syntax error, it's probably a missing AND in the first subquery of ... You can also use an Outer Join and select all the records which will have ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Method annotation and anonymous functions
    ... anonymous functions and anonymous closures (so def and lambda could have ... the same syntax but lambda would create a closure, ... class Foo ...
    (comp.lang.ruby)