Thanks David.
I didn't realize that the column names in the subquery would be scoped
to the outer query table. I thought one *had* to use an alias for that
and by default the column names were always scoped to the inner query
(and would error out otherwise).
Definitely learnt something new today..thanks again for the reply!
Best,
--H
David G. Johnston wrote:
> On Sunday, January 15, 2017, Hursh Jain <hurshj@gmail.com
> <mailto:hurshj@gmail.com>> wrote:
>
>
> SELECT * from property
> WHERE pid in (
> SELECT pid FROM reward WHERE reward_type = 'DAILYPROMO_WIN')
> ;
>
> And this also runs (again taking a long time) but without any
> errors. How can this be ? (there is no pid in the reward table, so
> why does the
> subquery not throw an error)?
>
>
>
> The "pid" column is the one from the outer query property table - this
> is a correlated subquery that you've written.
>
> As long as one record with that reward_type exists your query devolves
> to "where true" thus making indexes useless and returning every row in
> property.
>
> David J.
>