7

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

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

-- Method 2
SELECT
a.id,
b.id
FROM a
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.

8
  • 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
    Commented Jul 29, 2021 at 14:11
  • Ok, I edit it to be more relevant.
    – GeoGyro
    Commented Jul 29, 2021 at 14:13
  • So the two syntax are the same (as the query plan said), no writing recommendations ?
    – GeoGyro
    Commented Jul 29, 2021 at 14:39
  • 1
    inner join is just syntactic sugar sprinkled over the old style from a,b where..
    – Ian Turton
    Commented 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
    Commented Jul 29, 2021 at 14:45

1 Answer 1

6

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)
4
  • why not the same with a left join ? Commented Mar 4 at 20:44
  • @CristiánVargasAcevedo left join implies an OUTER join, which is different. It keeps all the rows from the left table including the rows that don't match the join condition. An inner join only keeps the rows that match the join condition. postgresqltutorial.com/postgresql-tutorial/postgresql-joins
    – jbalk
    Commented Mar 5 at 0:09
  • In a test for both cases on spark sql with sedona i get same results....Note In left case add a condition for right table id is not null Commented Mar 6 at 1:48
  • @CristiánVargasAcevedo it's not the same result if you have to add another condition. It's a different join type
    – jbalk
    Commented Mar 6 at 17:09

Your Answer

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

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