Kevin Grittner <kgrittn@ymail.com> wrote:
> 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
Perhaps you were looking for a way to formulate it something like
this?:
test=# SELECT *
test-# FROM tab1 a
test-# LEFT JOIN LATERAL
test-# (
test(# SELECT *
test(# FROM tab2 b
test(# WHERE EXISTS
test(# (
test(# SELECT *
test(# FROM tab3 c
test(# WHERE c.k = a.i
test(# )
test(# ) AS SJ_subquery
test-# ON true;
i | j
---+---
1 | 4
1 | 5
1 | 6
2 |
3 | 4
3 | 5
3 | 6
(7 rows)
Without LATERAL you get an error:
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 11: WHERE c.k = a.i
^
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company