Re: good style?

> -----Original Message-----
> From: Rafal Kedziorski [mailto:rafcio(at)polonium(dot)de]
> Sent: Friday, February 21, 2003 3:30 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] good style?
>
>
> hi,
>
> I have 8 tables and this query:
>
> select u.users_id, m.name as mandant_name, u.login_name, u.password,
> u.first_name, u.last_name, u.creation_date, g.name as groups_name,
> ae.acl_entry_id, a.name as acl_name, p.name as permission_name
> from mandant m, users_2_groups u2g, groups g, users u,
> permission p,
> acl a, acl_entry ae, groups_2_acl_entry g2ae
> where m.mandant_id = u.mandant_id and
> u2g.groups_id = g.groups_id and
> u2g.users_id = u.users_id and
> g2ae.groups_id = g.groups_id and
> g2ae.acl_entry_id = ae.acl_entry_id and
> ae.acl_id = a.acl_id and
> ae.permission_id = p.permission_id
>
> I'm not using JOIN for get this information. would be JOIN a
> better sql
> programming style? faster?
>

As there is no outer join syntax to use in WHERE, you need to write LEFT JOINs anyway. And in this case it looks better if you write all joins as JOIN clauses.

When using JOIN you are directing Postgres to use exactly this join order. I found it preferrable over letting query optimizer to decide. Generally you know better what tables will contain more rows and what less. It's more important in development phase, because there is usually not much test data and all tables look the same to optimizer.

There are few cases, when it's better to join in WHERE. For example when you have 3 tables, all joined sequentially, and you sometimes filter by field in table1, sometimes by field in table3. When you fix join order by using JOINS then one of the queries may perform bad. When you join tables in WHERE, the optimizer chooses whether it should join table1 and table2 first or table3 and table2 first. The former is better when filtering by field in table1, the latter is better when filtering by field in table3.

Tambet