Mixing Implicit and Explicit Joins

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.

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.

Avoid mixing implicit and explicit joins in PostgreSQL and JSON data

Use a lateral join:

SELECT table1.id, json_data1.*
FROM table1
  cross join lateral jsonb_array_elements(json_column->'json_data1') as json_data1 
  cross join lateral jsonb_array_elements(json_data1 ->'json_data2') json_data2
  JOIN table2 ON table2.id = table1.tb2_id

Mixing explicit and implicit joins fails with There is an entry for table ... but it cannot be referenced from this part of the query

The SQL spec states that explicit joins are performed before implicit joins. This is an implicit join:

FROM table1 t1, table2 t2 WHERE t1.id=t2.t1id

This is an explicit join:

FROM table1 t1 JOIN table2 t2 ON (t1.id=t2.t1id)

This code bit:

categories c 
     LEFT JOIN photos p 
        ON p.referencekey = i.key 

is an explicit join and is run first. Note that at this point the table aliased as i hasn't been looked at yet, so it can't be joined yet. Note that MySQL fixed this behaviour in 5.2 I believe, and this query will no longer work there either.

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

MariaDB JOIN syntax

Don't mix implicit and explicit joins! Bad things happen when you do that, because different types of joins have different precedence rules: explicit joins are evaluated first, which causes the error you are getting. At the time when the left join is interpreted, aliases defined in the implicit joins have not been seen yet.

Matter of fact, use explicit, standard joins consistently, in all your queries: implicit joins are legacy syntax, that should not be used in new code.

SELECT u.name AS Username,
       a.description AS Activity,
       r.role AS Role
FROM users u
INNER JOIN users_roles ur ON ur.user_id = u.id
INNER JOIN roles r ON r.id = ur.role_id
LEFT JOIN activity a ON a.user_id = u.id

Doing a left join with old style joins

The problem is (not sure if it's standard but most database engines seem to follow it) explicit joins are processed before implicit joins. At the point at which you're doing your join, the only tables/aliases which are in scope are table6alias and alias3. But you're trying to reference table1 in your ON clause for the join.

As you suspected, the solution is to use explicit joins throughout, which also gives you more control over the order in which joins happen.

(Or the quick fix would be to put the LEFT JOIN to alias3 immediately after table1)

---

Related Topics