Does PostgreSQL always implement FROM ... WHERE in the same way as FROM ... JOIN ... ON ...?

Question

1. In PostgreSQL document, there are two examples:

   EXPLAIN SELECT *
   FROM tenk1 t1, tenk2 t2
   WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
                                           QUERY PLAN
   ------------------------------------------------------------------------------------------
    Hash Join  (cost=230.47..713.98 rows=101 width=488)
      Hash Cond: (t2.unique2 = t1.unique2)
      ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
      ->  Hash  (cost=229.20..229.20 rows=101 width=244)
            ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244)
                  Recheck Cond: (unique1 < 100)
                  ->  Bitmap Index Scan on tenk1_unique1 
    (cost=0.00..5.04 rows=101 width=0)
                        Index Cond: (unique1 < 100)
   

   and

   EXPLAIN SELECT *
   FROM tenk1 t1, onek t2
   WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
                                           QUERY PLAN
   ------------------------------------------------------------------------------------------
    Merge Join  (cost=198.11..268.19 rows=10 width=488)
      Merge Cond: (t1.unique2 = t2.unique2)
      ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)
            Filter: (unique1 < 100)
      ->  Sort  (cost=197.83..200.33 rows=1000 width=244)
            Sort Key: t2.unique2
            ->  Seq Scan on onek t2  (cost=0.00..148.00 rows=1000 width=244)
   

   Hash join and merge join are ways to implement joins (explicitly in SQL, FROM ... JOIN ... ON ... ).

   Does PostgreSQL implement FROM ... WHERE in the above examples in the same way as FROM ... JOIN ... ON ...? Does it always do so?

2. Generally in PostgreSQL, I was wondering when to use FROM ... JOIN ... ON ... and when to use ... FROM ... WHERE ..., when both seem equivalent effectively?

Thanks.

I read some similar posts for other RDBMS such as INNER JOIN ON vs WHERE clause, SQL join: where clause vs. on clause, WHERE Clause vs ON when using JOIN, and Filtering JOINs: WHERE vs. ON. Most of them talk about

• human readability,

• inner join vs outer join, also see In outer join, where does a plain filter condition come from?

• FROM ... WHERE ... will implement WHERE selection on the result of FROM cartesian product, while FROM ... JOIN ... ON ... will implement ON selection during creating cartesian product.

Answer 1

If two queries mean the same, then postgresql tries to execute them in the same way. However, that doesn't always work. It's the goal, but it doesn't work always, mostly because the planner doesn't realise that the two necessarily at the same. There may be deficiencies in your database schema, and of course there may be bugs or insufficient insight in the query planner.

Rewriting a heavily used query to an equivalent form that executes better is quite common. (One of the reasons that I personally like postgresql so much is that the rewritten query is usually a better expression of my intent than my original query was.)

The answer to your question may be summed up as "yes in theory and often in practice".