The first SQL Query is using what's known as an implicit join (A.K.A comma join).
The join clause is replaced by a comma, and the join criteria is specified in the where clause.
The second SQL Query is using what's known as an explicit join.
The join criteria has it's own dedicated clause (the ON clause).
BTW, these queries are not the same: The first is an inner join and the second is a left outer join.
Implicit joins requires a little less typing, but that's probably the only benefit over explicit joins.
Explicit joins are a part of the ANSI standard for almost 30 years now, and with good reasons too:
Explicit joins provides a clear distinction between join conditions and filter conditions.
Inner joins can easily be replaced to an outer (left, right or full) join simply by replacing (or adding) a keyword - implicit outer joins are no longer supported at least on one major rdbms - SQL Server. (they where deprecated in 2008 version, which ended it's extended support in July 2019).
Explicit joins are far less error prone - since the join condition is in the on clause, the compiler will not let you run a join query without specifying a join condition.
Explicit joins are far more readable and maintainable.
With multiple joins in a query, explicit joins provides a very easy way to distinguish which condition belongs to which join.
WOW!. Thanks for pointing out the mistake. Thanks for the clarification. I would go with implicit join if I just want to execute some queries that are used only once, let's say in the console.
What you call less noise I call more information.
The first SQL Query is using what's known as an implicit join (A.K.A comma join).
The join clause is replaced by a comma, and the join criteria is specified in the where clause.
The second SQL Query is using what's known as an explicit join.
The join criteria has it's own dedicated clause (the
ONclause).BTW, these queries are not the same: The first is an inner join and the second is a left outer join.
Implicit joins requires a little less typing, but that's probably the only benefit over explicit joins.
Explicit joins are a part of the ANSI standard for almost 30 years now, and with good reasons too:
Explicit joins provides a clear distinction between join conditions and filter conditions.
Inner joins can easily be replaced to an outer (left, right or full) join simply by replacing (or adding) a keyword - implicit outer joins are no longer supported at least on one major rdbms - SQL Server. (they where deprecated in 2008 version, which ended it's extended support in July 2019).
Explicit joins are far less error prone - since the join condition is in the on clause, the compiler will not let you run a join query without specifying a join condition.
Explicit joins are far more readable and maintainable.
With multiple joins in a query, explicit joins provides a very easy way to distinguish which condition belongs to which join.
WOW!. Thanks for pointing out the mistake. Thanks for the clarification. I would go with implicit join if I just want to execute some queries that are used only once, let's say in the console.
Glad to help :-)