Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Joins: Future of the SQL ANSI Standard (where vs join)?

We are developing ETL jobs and our consultant has been using "old style" SQL when joining tables

select a.attr1, b.attr1
from table1 a, table2 b
where a.attr2 = b.attr2

instead of using the inner join clause

select a.attr1, b.attr1
from table1 as a inner join table2 as b
   on a.attr2 = b.attr2

My question is that in the long run, is there a risk for using the old "where join"? How long this kind of joins are supported and kept as ANSI standard? Our platform is SQL Server and my primary cause is that in the future these "where joins" are no longer supported. When this happens, we have to modify all our ETL jobs using "inner join" style of joins.

like image 998
atricapilla Avatar asked Sep 10 '10 11:09

atricapilla


2 Answers

Rather than worry about some possible risk in the future, why not worry about the risk you face right now?

In addition to Mark's points:

  • The code is harder to read (and thus understand the purpose of) when the ON clauses are disconnected (sometimes by many lines) from the joined tables. This increases the likelihood of errors when modifying the code.
  • Determining what kind of JOIN is being done is harder - you have to go surfing through the WHERE clause and hope that what you see is right.
  • Finding missing JOIN clauses is much harder, increasing the risk of an inadvertent Cartesian join - when you use ANSI syntax, the ON clauses line up nicely, making this trivial.
like image 175
D'Arcy Rittich Avatar answered Oct 09 '22 23:10

D'Arcy Rittich


I doubt that "where joins" would ever be unsupported. It's just not possible to not support them, because they are based on Cartesian products and simple filtering. They actually aren't joins.

But there are many reasons to use the newer join syntax. Among others:

  • Readability
  • Maintainability
  • Easier change to outer joins
like image 21
Stefan Steinegger Avatar answered Oct 10 '22 01:10

Stefan Steinegger