Re: question about efficiency

On Mon, 11 Nov 2002, Johnson, Shaunn wrote:

> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
>
> Have a question about a query and it's efficiency:
>
> One of the users created a query that looks like this:
>
> [snip]
> "create table dev_lbpclaimsum as
> SELECT
>   claimsum2001.c_contract_num,
>   claimsum2001.c_mbr_num,
>   claimsum2001.c_proc_cd,
>   claimsum2001.c_proc_mod,
>   claimsum2001.d_from_dt,
>   claimsum2001.d_thru_dt,
>   claimsum2001.i_pd,
>   claimsum2001.c_serv_prov
> FROM
>   claimsum2001 a join dev_pb_proc b on a.c_proc_cd = substr
> (b.c_proc_cd,1,6)

This query is (afaik) illegal in plain SQL and for postgres assumes you
want a join with claimsum2001 as claimsum2001. See notice,
> psql:./jans_stuff.sql:14: NOTICE:  Adding missing FROM-clause entry for
> table "claimsum2001"

It's basically converted it into a three way join.

> Can someone explain why there's a difference between using
> claimsum2001.(whatever) and a.(whatever)?

Once you alias claimsum2001 as a there isn't a table claimsum2001
in the query, but you're asking for columns from the non-existant
table in the select clause.  PostgreSQL tries to be helpful by
adding a from entry for the table whereas it'd technically be illegal.