In PostgreSQL/PostGIS, when running an intersection between 2 geometries, what the difference between these two syntaxes ?

-- Method 1
FROM a, b
WHERE st_intersects(a.geom, b.geom)

-- Method 2
INNER JOIN b ON st_intersects(a.geom, b.geom)

In real conditions, first method is longer to execute than second for the same result. Instinctively I would have believed the opposite because the first had one condition, how to explain this ?

An EXPLAIN ANALYZE SELECT with the unrolled detail did not enlighten me more (here is an example with real tables, the names do not match the example above).

Method 1 where

Method 2 inner_join

I'm using PostgreSQL 13.2 and PostGIS 3.1.1.

  • The title is somewhat misleading. This isn't about syntax as much as it is about query organization. You should include the EXPLAIN plans for the two queries.
    – Vince
    Jul 29, 2021 at 14:11
  • Ok, I edit it to be more relevant.
    – GeoGyro
    Jul 29, 2021 at 14:13
  • So the two syntax are the same (as the query plan said), no writing recommendations ?
    – GeoGyro
    Jul 29, 2021 at 14:39
  • 1
    inner join is just syntactic sugar sprinkled over the old style from a,b where..
    – Ian Turton
    Jul 29, 2021 at 14:44
  • 2
    My comment just got deleted for whatever reasons...my point had been that the query plan got cached after the first run and reused, thus the faster execution. The planner is free to restructure and inline statements like JOINs and filters, and the finally executed query is likely identical in both cases.
    – geozelot
    Jul 29, 2021 at 14:45

1 Answer 1


a INNER JOIN b ON is the same as a JOIN b ON, which is the same as SELECT FROM a,b WHERE. Your 2 queries are functionally identical.

...FROM a,b WHERE st_intersects(a.geom,b.geom)

is the same as

...FROM a INNER JOIN b ON st_intersects(a.geom,b.geom)

is the same as

...FROM a JOIN b ON st_intersects(a.geom,b.geom)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.