Postgres JOIN conditions vs WHERE conditions

Logically, it makes no difference at all whether you place conditions in the join clause of an INNER JOIN or the WHERE clause of the same SELECT. The effect is the same.

(Not the case for OUTER JOIN!)

While operating with default settings it also makes no difference for the query plan or performance. Postgres is free to rearrange joins and JOIN & WHERE conditions in its quest for the best query plan - as long as the number of tables is not greater than the join_collapse_limit (default 8). Details:

• Complex view becomes slow when adding ORDER BY in outer query with small LIMIT

For readability and maintainability it makes sense to place conditions that connect tables in the respective JOIN clause and general conditions in the WHERE clause.

Your query looks just fine. I would use table aliases to cut back the noise, though.

Minor detail:

int2 '1' or even 1::int2 are more sensible than (1)::INT2. And while comparing to a value of well defined numeric data type, a plain numerical constant 1 is good enough, too.

A couple of points..

1. If you're joining on a condition by the same name (user_id) in your case, you can use USING (user_id) rather than ON (a.user_id = b.user_id). This also saves a redundant column from potentially being outputted (if you're running SELECT * in production).

2. 1::int2 is problematic. Either status, and is_primary and others are already int2 in which case the literal 1 will be automatically be casted to int2, or int2 casted to int as pg sees fit. Or, if you're storing them as regular ints, and casting them down as if that made a difference in computation -- which it doesn't, the cast alone makes that a losing proposition.

3. When possible, all of the ::int2 should probably be stored as boolean. Then you can write your WHERE condition to be simpler too.

4. For your type and status, you may want an ENUM type.