title: SQL: ON vs. WHERE in sub JOIN
body: <p>What is the difference between using ON and WHERE in a sub join when using an outer reference?</p>
  <p>Consider these two SQL statements as an example (looking for 10 persons with not closed tasks, using <code>person_task</code> with a many-to-many relationship):</p>
  <pre><code>select p.name
  from person p
  where exists (
      select 1
      from person_task pt
      join task t on pt.task_id = t.id 
                 and t.state &lt;&gt; 'closed'
                 and pt.person_id = p.id -- ON
  )
  limit 10
  </code></pre>
  <hr />
  <pre><code>select p.name
  from person p
  where exists (
      select 1
      from person_task pt
      join task t on pt.task_id = t.id and t.state &lt;&gt; 'closed'
      where pt.person_id = p.id -- WHERE
  )
  limit 10
  </code></pre>
  <p>They produce the same result but the statement with ON is considerably faster.</p>
  <p>Here the corresponding <code>EXPLAIN (ANALYZE)</code> statements:</p>
  <pre><code>-- USING ON
  Limit  (cost=0.00..270.98 rows=10 width=8) (actual time=10.412..60.876 rows=10 loops=1)
    -&gt;  Seq Scan on person p  (cost=0.00..28947484.16 rows=1068266 width=8) (actual time=10.411..60.869 rows=10 loops=1)
          Filter: (SubPlan 1)
          Rows Removed by Filter: 68
          SubPlan 1
            -&gt;  Nested Loop  (cost=1.00..20257.91 rows=1632 width=0) (actual time=0.778..0.778 rows=0 loops=78)
                  -&gt;  Index Scan using person_taskx1 on person_task pt  (cost=0.56..6551.27 rows=1632 width=8) (actual time=0.633..0.633 rows=0 loops=78)
                        Index Cond: (id = p.id)
                  -&gt;  Index Scan using taskxpk on task t  (cost=0.44..8.40 rows=1 width=8) (actual time=1.121..1.121 rows=1 loops=10)
                        Index Cond: (id = pt.task_id)
                        Filter: (state &lt;&gt; 'open')
  Planning Time: 0.466 ms
  Execution Time: 60.920 ms
  </code></pre>
  <hr />
  <pre><code>-- USING WHERE
  Limit  (cost=2818814.57..2841563.37 rows=10 width=8) (actual time=29.075..6884.259 rows=10 loops=1)
    -&gt;  Merge Semi Join  (cost=2818814.57..59308642.64 rows=24832 width=8) (actual time=29.075..6884.251 rows=10 loops=1)
          Merge Cond: (p.id = pt.person_id)
          -&gt;  Index Scan using personxpk on person p  (cost=0.43..1440340.27 rows=2136533 width=16) (actual time=0.003..0.168 rows=18 loops=1)
          -&gt;  Gather Merge  (cost=1001.03..57357094.42 rows=40517669 width=8) (actual time=9.441..6881.180 rows=23747 loops=1)
                Workers Planned: 2
                Workers Launched: 2
                -&gt;  Nested Loop  (cost=1.00..52679350.05 rows=16882362 width=8) (actual time=1.862..4207.577 rows=7938 loops=3)
                      -&gt;  Parallel Index Scan using person_taskx1 on person_task pt  (cost=0.56..25848782.35 rows=16882362 width=16) (actual time=1.344..1807.664 rows=7938 loops=3)
                      -&gt;  Index Scan using taskxpk on task t  (cost=0.44..1.59 rows=1 width=8) (actual time=0.301..0.301 rows=1 loops=23814)
                            Index Cond: (id = pt.task_id)
                            Filter: (state &lt;&gt; 'open')
  Planning Time: 0.430 ms
  Execution Time: 6884.349 ms
  </code></pre>
  <p>Should therefore always the ON statement be used for filtering values in a sub JOIN? Or what is going on?
  I have used Postgres for this example.</p>