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?

  • 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 Feb 4 '17 at 14:06
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.

  • is one considered faster than the other? – Stophface Feb 4 '17 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 Feb 4 '17 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 – LR1234567 Feb 4 '17 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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