Beyond knowing the difference between the JOINS
explicit and implicit ones, I want to know which ones are more efficient and performing?
Beyond knowing the difference between the JOINS
explicit and implicit ones, I want to know which ones are more efficient and performing?
Is there a performance difference?
No. There is no performance difference between an implicit or explicit join. As long as they are equivalent queries, the database engine will execute the queries in the same way. (Note: differences can sometimes occur due to a bug in the database engine. I remember this happening in some older versions of Oracle. But it was really just that: a bug that was eventually fixed)
Really, the only difference between implicit and explicit join is the syntax itself.
Is there an advantage to using one notation rather than the other?
Yes. It is always better to use explicit joins.
Reasons:
In the SQL-89 standard, only implicit notation existed. But in 1992 (25 years ago!), with SQL-92, explicit notation was introduced. Since then, it is commonly accepted that the norm should be to use the new explicit notation, although in reality, since so much time has passed, it can no longer be considered new.
To understand this second point better, let's take this query with explicit joins as an example:
...and let's compare it to its equivalent using implicit joins:
Although this example is very simple, it can already be seen that the explicit join notation communicates very well what the conditions are that join the different tables. In contrast, with the implicit notation it is not immediately obvious how the 3 tables are related. And the more complex the query, the less clear and more confusing it is.
Worse still, with implicit notation, especially with more complex queries, it is very easy to forget a join condition, for example:
In this query, because we forgot about the condition
c.bid = b.id
, we are accidentally creating a Cartesian plan withTableC
.In contrast, due to the explicit join syntax, it is very difficult to make that mistake, because for each join, we are forced to define a clause
ON
, otherwise the query will not run.Again, if we take the following example with implicit join:
...but now we want the join with to
TableC
be a left join instead of an inner join. In various database engines, in that case, there is no way to define it unless we use the explicit notation. And in cases where there is an implicit notation for the database engine, that notation falls outside of the SQL standard, and you are not served by other database engines.For example, it is true that Oracle allows you to define the left join in this way using the
(+)
:...but this syntax only works in Oracle. And even in Oracle, it is considered deprecated. Also, it is very easy to make a mistake, especially when the join condition is a little more complex.
In contrast, if we look at the left join using the explicit notation:
... we note that all that is needed for it to be left joined with
TableC
is to changejoin
toleft join
, assuming we start from the explicit notation. It is very simple without greater risk of errors.