sql – What is the difference between implicit and explicit joins?

Question:

Beyond knowing the difference between explicit and implicit JOINS I want to know which ones are more efficient and performant?

Answer:

Is there a difference in performance?

No. There is no performance difference between an implicit or an explicit join. As long as they are equivalent queries, the database engine will execute the queries in the same way. (Note: sometimes differences can occur due to a defect in the database engine. I remember this happened in some older versions of Oracle. But in reality it was just that: a defect that was eventually corrected)

Really, the only difference between implicit or explicit join is the syntax itself.

Is there an advantage of using one notation rather than the other?

Yes. It is always better to use explicit joins.

Reasons:

  1. Because for decades the syntax of implicit joins has been considered obsolete.

In the SQL-89 standard, only the 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 it has been so long, it can no longer be considered new.

  1. Because when the query is more complex, it is very easy to make mistakes when defining join conditions.

To understand this second point better, let's take this query with explicit joins as an example:

select a.colA, b.colB, c.colC
  from TableA a
  join TableB b on b.aid = a.id
  join TableC c on c.bid = b.id
 where a.colA like '%abc%'

… and let's compare it to its equivalent using implicit joins:

select a.colA, b.colB, c.colC
  from TableA a, TableB b, TableC c
 where a.colA like '%abc%'
   and b.aid = a.id
   and c.bid = b.id

Although this example is very simple, it can already be seen that the explicit join notation communicates very well what are the conditions 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 the implicit notation, especially with more complex queries, it is very easy to forget a join condition, for example:

select a.colA, b.colB, c.colC
  from TableA a, TableB b, TableC c
 where a.colA like '%abc%'
   and b.aid = a.id

In this query, because we forgot about the condition c.bid = b.id , we are accidentally creating a Cartesian plan with TableC .

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 an ON clause, otherwise the query will not run.

  1. Because it is the only way within the SQL standard to define an outer join (left or right join).

Again, if we take the following example with implicit join:

select a.colA, b.colB, c.colC
  from TableA a, TableB b, TableC c
 where a.colA like '%abc%'
   and b.aid = a.id
   and c.bid = b.id

… but now we want the join with 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 is outside the SQL standard, and other database engines do not work for you.

For example, it is true that Oracle allows you to define the left join in this way using the (+) :

select a.colA, b.colB, c.colC
  from TableA a, TableB b, TableC c
 where a.colA like '%abc%'
   and b.aid = a.id
   and c.bid (+) = b.id

… but this syntax only works in Oracle. And even in Oracle, it is considered obsolete. Furthermore, it is very easy to make mistakes, especially when the join condition is slightly more complex.

In contrast, if we look at the left join using the explicit notation:

select a.colA, b.colB, c.colC
  from TableA a
  join TableB b on b.aid = a.id
  left join TableC c on c.bid = b.id
 where a.colA like '%abc%'

… we notice that the only thing that is needed for a left join with TableC is to change the join to a left join , assuming that we start from the explicit notation. It is very simple without major risk of errors.

Scroll to Top