0

It's often convenient to write:

SELECT * 
FROM t1   # ... +many more tables
INNER JOIN t2 ON (t1.id = t2.col)
INNER JOIN t3 ON (t1.id = t3.col)
INNER JOIN t4 ON (t1.id = t4.col)
...

as a cross join with conditionals:

SELECT * 
FROM t1, t2, t3, t4   # ... +many more tables
WHERE
       t1.id = t2.col
   AND t1.id = t3.col
   AND t1.id = t4.col
   # +include matches on columns of other tables

However, a naive implementation of a cross join would have a higher time complexity than the inner join. Does Postgres optimize the second query into one with the same time complexity as the first?

  • 3
    Check the execution plan and you will know – a_horse_with_no_name Feb 17 '18 at 20:46
  • 1
    Why is it convenient? – dezso Feb 17 '18 at 21:53
  • Because as the queries get more complex, its easier to read through a single list list of conditions than many separate lists with varying indentation. -- INNER JOIN t2 ON (t1.id = t2.col) WHERE t2conditions.. – user48956 Feb 17 '18 at 21:56
4

I would advise to use explicit JOIN syntax, but reduce the noise:

SELECT *  -- really? *all* columns?
FROM   t1
JOIN   t2 ON t1.id = t2.col
JOIN   t3 ON t1.id = t3.col
JOIN   t4 ON t1.id = t4.col
-- ... more tables
WHERE ...

Place conditions linking two tables in the JOIN clause.
Place other conditions in the WHERE clause.

INNER is a noise word. Parentheses are not required around join conditions.

But to answer your question:

Are implicit joins as efficient as explicit joins in Postgres?

Yes. There is no difference in efficiency. Postgres is typically free to rearrange the order of join operations and apply JOIN and WHERE conditions in any order it sees fit. The manual:

Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN) is semantically the same as listing the input relations in FROM, so it does not constrain the join order.

There are special considerations for OUTER joins, that do not apply here.
And be aware of some limitations:

Since you mentioned:

+many more tables

... you probably need to consider how to optimize planning time and query execution time by carefully choosing the order of explicit joins and placement of conditions. You may want to use subqueries, CTEs, mix explicit and implicit joins, use parentheses among groups of FROM clause items or play with configuration settings to get best results.

Related:

| improve this answer | |

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.