Hacker News Reader

Comment by listenallyall | Hacker News Reader

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

View on HN

AFAIK 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.

Perhaps 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?

In 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.

I personally prefer being explicit in this case. Seeing the word INNER triggers the right way to visualize the query in my head.

Reply on news.ycombinator.com