Re: Reference to parent query from ANY sublink

Antonin Houska <antonin(dot)houska(at)gmail(dot)com> wrote:

> SELECT *
> FROM    tab1 a
>     LEFT JOIN
>     tab2 b
>     ON a.i = ANY (
>         SELECT  k
>         FROM    tab3 c
>         WHERE    k = a.i);

This query works with k in any or all tables, but the semantics
certainly vary depending on where k happens to be.  It would help a
lot if you showed SQL statements to create and populate the tables
involved and/or if you qualified all referenced column names with
the table alias to avoid ambiguity.

If I assume that the k reference is supposed to be a column in
tab3, what you have is a query where you always get all rows from
tab1, and for each row from tab1 you either match it to all rows
from tab2 or no rows from tab2 depending on whether the tab1 row
has a match in tab3.

test=# create table tab1 (i int);
CREATE TABLE
test=# create table tab2 (j int);
CREATE TABLE
test=# create table tab3 (k int);
CREATE TABLE
test=# insert into tab1 values (1), (2), (3);
INSERT 0 3
test=# insert into tab2 values (4), (5), (6);
INSERT 0 3
test=# insert into tab3 values (1), (3);
INSERT 0 2
test=# SELECT *
FROM    tab1 a
    LEFT JOIN
    tab2 b
    ON a.i = ANY (
        SELECT  k        
        FROM    tab3 c
        WHERE    k = a.i);
 i | j
---+---
 1 | 4
 1 | 5
 1 | 6
 2 |
 3 | 4
 3 | 5
 3 | 6
(7 rows)

> SELECT  *
> FROM    tab1 a
>     LEFT JOIN
>     (
>       SELECT *
>       tab2 b
>       SEMI JOIN
>       (  SELECT  k
>           FROM    tab3 c
>           WHERE  k = a.i
>       ) AS ANY_subquery
>       ON a.i = ANY_subquery.k
>     ) AS SJ_subquery
>     ON true;

It is hard to see what you intend here, since this is not valid
syntax.  I assume you want a FROM keyword before the tab2
reference, but it's less clear what you intend with the SEMI JOIN
syntax.  PostgreSQL supports semi-joins; but that is an
implementation detail for the EXISTS or IN syntax.  Could you
clarify your intent?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company