Re: Why does this SQL work?

2015-05-11 19:26 GMT+03:00 Anil Menon <gakmenon(at)gmail(dot)com>:

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

Because you can reference both, inner and outer columns from the inner
query.
Here you're most likely referring to the outer `msgid` in the subquery.

That's why it is always a good idea to prefix all your columns with tables
aliases.

--
Victor Y. Yegorov