ANSI vs. non-ANSI SQL JOIN syntax?

The two queries are the same, except the second is ANSI-92 SQL syntax and the first is the older SQL syntax which didn't incorporate the join clause. They should produce exactly the same internal query plan, although you may like to check You should use the ANSI-92 syntax for several of reasons The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand It doesn't matter with this particular query, but there are a few circumstances where the older outer join syntax (using + ) is ambiguous and the query results are hence implementation dependent - or the query cannot be resolved at all. These do not occur with ANSI-92 It's good practice as most developers and dba's will use ANSI-92 nowadays and you should follow the standard.

Certainly all modern query tools will generate ANSI-92 Myself I resisted ANSI-92 for some time as there is a slight conceptual advantage to the old syntax as it's a easier to envisage the SQL as a mass Cartesian join of all tables used followed by a filtering operation - a mental technique that can be useful for grasping what a SQL query is doing. However I decided a few years ago that I needed to move with the times and after a relatively short adjustment period I now strongly prefer it - predominantly because of the first reason given above.

The two queries are the same, except the second is ANSI-92 SQL syntax and the first is the older SQL syntax which didn't incorporate the join clause. They should produce exactly the same internal query plan, although you may like to check. You should use the ANSI-92 syntax for several of reasons The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand.It doesn't matter with this particular query, but there are a few circumstances where the older outer join syntax (using + ) is ambiguous and the query results are hence implementation dependent - or the query cannot be resolved at all.

These do not occur with ANSI-92 It's good practice as most developers and dba's will use ANSI-92 nowadays and you should follow the standard. Certainly all modern query tools will generate ANSI-92. Myself I resisted ANSI-92 for some time as there is a slight conceptual advantage to the old syntax as it's a easier to envisage the SQL as a mass Cartesian join of all tables used followed by a filtering operation - a mental technique that can be useful for grasping what a SQL query is doing.

However I decided a few years ago that I needed to move with the times and after a relatively short adjustment period I now strongly prefer it - predominantly because of the first reason given above.

1 Can I add: helps avoid accidental cross joins... – gbn Oct 21 '09 at 17:37.

The two queries are equal - the first is using non-ANSI JOIN syntax, the 2nd is ANSI JOIN syntax. I recommend sticking with the ANSI JOIN syntax. And yes, LEFT OUTER JOINs (which, btw are also ANSI JOIN syntax) are what you want to use when there's a possibility that the table you're joining to might not contain any matching records.

Reference: Conditional Joins in SQL Server.

Execute both and check their query plans. They should be equal.

1 +1 to using the analysis tools. I would recommend looking at: Let the standards be your guide, but remember that your particular DBs implementation is the path (and hopefully it's not MySQL :) – pst Oct 21 '09 at 6:43.

The second construct is known as the "infixed join syntax" in the SQL community. The first construct AFAIK doesn't have widely accepted name so let's call it the 'traditional' syntax. The usual arguments go like this: Pros of the 'Traditional' syntax: the predicates are physically grouped together in the WHERE clause in whatever order which makes the query generally, and n-ary relationships particularly, easier to read and understand (the ON clauses of the infixed syntax can spread out the predicates so you have to look for the appearance of one table or column over a visual distance).

Cons of the 'Traditional' syntax: There is no parse error when omitting one of the 'join' predicates and the result is a Cartesian product (known as a CROSS JOIN in the infixed syntax) and such an error can be tricky to detect and debug. Also, 'join' predicates and 'filtering' predicates are physically grouped together in the WHERE clause, which can cause them to be confused for one another.

ANSI syntax does enforce neither predicate placement in the proper clause (be that ON or WHERE), nor the affinity of the ON clause to adjacent table reference. A developer is free to write a mess like this SELECT C. FullName, C.

CustomerCode, O. OrderDate, O. OrderTotal, OD.

ExtendedShippingNotes FROM Customer C CROSS JOIN Order O INNER JOIN OrderDetail OD ON C. CustomerID = O. CustomerID AND C.

CustomerStatus = 'Preferred' AND O. OrderTotal > 1000.0 WHERE O. OrderID = OD.

OrderID; Speaking of query tools who "will generate ANSI-92", I'm commenting here because it generated SELECT 1 FROM DEPARTMENTS C JOIN EMPLOYEES A JOIN JOBS B ON C. DEPARTMENT_ID = A. DEPARTMENT_ID ON A.

JOB_ID = B. JOB_ID The only syntax that escapes conventional "restrict-project-cartesian product" is outer join. This operation is more complicated because it is not associative (both with itself and with normal join).

One have to judiciously parenthesize query with outer join, at least. However, it is an exotic operation; if you are using it too often I suggest taking relational database class.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.