| From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
|---|---|
| To: | Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com> |
| Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Strange behavior |
| Date: | 2020-10-10 16:36:55 |
| Message-ID: | CA+bJJbyANDsdKiMNV_yiQ=1=K6OaUzHhH1puHGgv65Gkoyvz6A@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Olivier:
On Sat, Oct 10, 2020 at 6:13 PM Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com> wrote:
> I’m surprised by this behavior I noticed in pgadmin3 and postgresql 9.6
...
> select v1 from test1 where v1 not in (select v1 from test2)
This is called a correlated subquery ( google and search for it, it is
even in wikipedia ). It has many uses.
Basically, it refers to the v1 from the outside query.
Get in the habit of using (potentially aliased ) column names whenever
you have any moderately complex query, i.e. if the inner v1 would have
been v2 ( due to a typo ), writing your query as :
select t1.v1 from test1 as t1 wher t1.v1 not in ( select t2.v1 from
test2 as t2 )
Would have caught it.
Francisco Olarte.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David G. Johnston | 2020-10-10 17:58:04 | Re: Strange behavior |
| Previous Message | Olivier Leprêtre | 2020-10-10 15:59:11 | Strange behavior |