INNER JOIN vs MultiSelect in PostgreSQL

Question

Can somebody completely explain what is big difference in these two methods? Is there misunderstanding in database theory of programmers? Can somebody give a good article about the question or just say - what is a difference in these methods in PostgreSQL?

Use EXPLAIN to see the difference. PostgreSQL is pretty smart in executing queries, it might come up with the same queryplan for both types of queries. — Frank Heikens, Jun 28 '10 at 16:48
Perhaps related: http://stackoverflow.com/questions/2577174/sql-join-vs-subquery — leonbloy, Jun 28 '10 at 17:44

score 2 · Answer 1 · answered Jun 28 '10 at 16:35

2

Did you mean SELECT * FROM table1, table2 vs SELECT * FROM table1 JOIN table2 ON condition?

PostgreSQL optimizer makes this queries run with the same speed, but JOIN is more transparent and usable. Also, you can use LEFT/RIGHT JOIN.

answered Jun 28 '10 at 16:35

Андрей Костенко

1,032
8
13

The first version is also subject to accidental cross joins and the second is much more maintainable. – HLGEM Jun 28 '10 at 17:26

score 1 · Answer 2 · answered Jun 30 '10 at 23:12

In the PostgreSQL documentation there is a related topic. Explicit joins can give you more control over the execution order of statements using the join_collapse_limit GUC. Take a look at this page.

There are also all the other already mentioned advantages in readability and maintainability.

INNER JOIN vs MultiSelect in PostgreSQL

2 Answers2