9

I wonder where the difference between these two spatial joins is and, if there is a difference, when to use which. 

SELECT *
FROM points p, shapes s
WHERE ST_Within(p.geom, s.geom);

and

SELECT *
FROM points p
INNER JOIN shapes s ON ST_Within(p.geom, s.geom)

Is one faster in certain situations?

Improve this question
1
  • 1
    The difference is stylistic, but when your tasks expand to joining twelve to fifteen tables, you'll see why experienced SQL coders use JOIN exclusively. Unfortunately, as written, this is more of a pure SQL question, and opinion bait at that.
    – Vince
    Commented Feb 4, 2017 at 14:06

1 Answer 1

Reset to default
4

No one difference for both statement. Postgresql converts both it to the same query plan. You can see plans by just add EXPLAIN ANALYZE before your select and looks for result.

Improve this answer
3
  • is one considered faster than the other?
    – four-eyes
    Commented Feb 4, 2017 at 13:04
  • must be no. Postgresql converts both it to the same query plan. You can see plans by just add EXPLAIN ANALYZE before your select and looks for result.
    – Roman Tkachuk
    Commented Feb 4, 2017 at 13:13
  • 6
    I prefer the JOIN because it's too easy to forget the condition when mixed in WHERE. Also with the JOIN approach, you can use a LEFT to for example find points that are not within any shape. You can't do that with the WHERE
    – Regina Obe
    Commented Feb 4, 2017 at 14:53

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.