Explicit VS Implicit SQL Joins

Explicit vs implicit SQL joins

Performance wise, they are exactly the same (at least in SQL Server).

PS: Be aware that the IMPLICIT OUTER JOIN syntax is deprecated since SQL Server 2005. (The IMPLICIT INNER JOIN syntax as used in the question is still supported)

Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing

What's the purpose of an IMPLICIT JOIN in sql?

Fundamentally there is no difference between the implicit join and the explicit JOIN .. ON. Execution plans are the same.

I prefer the explicit notation as it makes it easier to read and debug.
Moreover, in the explicit notation you define the relationship between the tables in the ON clause and the search condition in the WHERE clause.

Mixing explicit and implicit joins

If I remember correctly, explicit joins are being processed before implicit joins, therefore - t2. is not available yet.

Solution: Avoid the use of implicit join syntax, and use the proper syntax of joins , just like the second part of your query.

IMPLICIT and EXPLICIT join

Explicit joins get their name from specifying explicitly what kind of join you use on the table (CROSS JOIN, INNER JOIN, LEFT OUTER JOIN etc.)

So you will have to re-write the query such that you replace the comma-separated tables in your FROM clause with explicit joins (INNER JOIN and LEFT JOIN here). Then move your join criteria to the ON clause in question:

select ...
from colleg
inner join p on p.num_colegiado  = coleg.num_colegiado
left join pd on pd.nif = p.nif and 
                pd.num_colegiado = p.num_colegiado and 
                pd.fecha_inicio <= sysdate and
                nvl(pd.fecha_fin, sysdate) >= sysdate
left join d on d.id_direccion = pd.id_direccion;

Which is better, implicit or explicit join?

In older days, the first was faster, but this is not true anymore. I personally think the INNER JOIN is better, because it is more readable. It shows better the relations between the table. You got those relations in the join, and you do the filtering in the WHERE clause. This separation makes the query more readable. But this is a matter of personal taste.

Mixing implicit and explicit JOINs

It results in an error because according to the SQL standard, the JOIN keyword has higher precedence than the comma. The sticky point is that table aliases are not usable until after the corresponding table has been evaluated in the FROM clause.

So when you reference e1 in your JOIN...ON expression, e1 doesn't exist yet.

Please stand by while I research Hibernate and find out if you can persuade it to use JOIN in all cases.

---

Hmm. Everything at Hibernate.org seems to be redirecting to jboss.org. So no way to read HQL documentation online right now. I'm sure they'll figure out their name serving eventually.

SQL Server - Implicit Join vs Explicit different results

Table A

+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+

Table B

+-----+-----------------+
| AID |   SomeString    |
+-----+-----------------+
|   1 | SomeOtherString |
|   2 | ABC             |
+-----+-----------------+

---

Query 1

SELECT *
FROM   TableA a
       LEFT JOIN TableB b
         ON a.ID = b.AID
            AND b.SomeString <> 'ABC'

This joins each row from A with every row from B matching a.ID = b.AID AND b.SomeString <> 'ABC'. If there were zero rows matching the row from A it is preserved in the result with NULL for the columns in table B (as happens for Id=2 below - the single row matching the id didn't meet the second condition and Id=3 where there was not even a row matching the id)

+----+------+-----------------+
| ID | AID  |   SomeString    |
+----+------+-----------------+
|  1 | 1    | SomeOtherString |
|  2 | NULL | NULL            |
|  3 | NULL | NULL            |
+----+------+-----------------+

Query 1.5

SELECT *
FROM TableA a
LEFT JOIN TableB b ON a.ID = b.AID

This changes the semantics slightly because now to be considered a match only the id predicate needs to be met not the string condition. The rows in A with ids 2 and 3 match rows in B under this new matching condition and only id=3 was added back in by the outer join.

+----+------+-----------------+
| ID | AID  |   SomeString    |
+----+------+-----------------+
|  1 | 1    | SomeOtherString |
|  2 | 2    | ABC             |
|  3 | NULL | NULL            |
+----+------+-----------------+

Query 2

SELECT *
FROM   TableA a
       LEFT JOIN TableB b
         ON a.ID = b.AID
WHERE  b.AID IS NULL
        OR b.SomeString <> 'ABC' 

This runs the WHERE b.AID IS NULL OR b.SomeString <> 'ABC predicate on the rows shown in the Query 1.5 results. Leaving you with two rows. The row preserved by the outer join is one of them.

+----+------+-----------------+
| ID | AID  |   SomeString    |
+----+------+-----------------+
|  1 | 1    | SomeOtherString |
|  3 | NULL | NULL            |
+----+------+-----------------+

---

Related Topics