3 ms·
The example uses the syntax FROM person AS p, gender AS j WHERE p.gender_id = j.gender_id Isn't it preferable to be explicit? Does some of the inefficienc
by listenallyall 11mo ago
The example uses the syntax
FROM person AS p, gender AS j
WHERE p.gender_id = j.gender_id
Isn't it preferable to be explicit? Does some of the inefficiency come from lack of explicitness?
FROM person as p
INNER JOIN gender as j
ON p.gender_id = j.gender_id
- pgaddict 11mo agoAFAIK these two joins are exactly the same once you get past the parsing. It's just a different way to write an inner join. It's translated into the same AST and so there's no difference in planning/execution.
- listenallyall 11mo agoPerhaps in this very basic case they are exactly the same but is that still true if you add secondary WHERE conditions that apply to just one table, or if one "table" is actually a view with a complex query definition, or many other ways in which a very simple "example" can quickly get complicated?
- Sesse__ 11mo agoIn general, you split up the WHERE condition at every top-level AND. Then you do either pullup or pushdown or both (it depends a bit on how your planner looks on the inside). In the end, you end up with the two cases being exactly the same before you start planning. For outer joins (left/right/full), it's different, and there you absolutely need the explicit join syntax (for correctness). And semijoins are not expressed the same way at all, partially for weird historical reasons.
- spprashant 11mo agoI personally prefer being explicit in this case. Seeing the word INNER triggers the right way to visualize the query in my head.