Re: Why does this SQL work?

On Tue, May 12, 2015 at 12:26:15AM +0800, Anil Menon wrote:
> manualscan=> select count(*) From msgtxt where msgid in (
> manualscan(>         select msgid From courier where org_id=3
> manualscan(>         )
> manualscan->  ;
>  count
> -------
>  10225
> (1 row)
> manualscan=> select count(*) From public.msgtxt where msgid in (select
> msgid From ver736.courier where org_id=3);
>  count
> -------
>  10225
> (1 row)
> Please note, there is no msgid col in courier table. Which brings the
> question why does this SQL work? An "select msgid From courier where
> org_id=3" by itself gives error column "msgid" does not exist.

This works because this is correlated subquery.

You should have always use aliases to avoid such errors. Like here:
select count(*) From msgtxt as m where m.msgid in (
    select c.msgid from courier c where c.org_id = 3
);

Your query is equivalent to:
select count(*) From msgtxt as m where m.msgid in (
    select m.msgid from courier c where c.org_id = 3
);
which returns all rows from msgtxt if there is at least one row in
courier with org_id = 3.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/