Informix 12.10.xC2 release has Lateral derived table support. “LATERAL” is a new keyword and it is required at beginning of a derived table . Lateral derived table allow user to access correlated columns inside derived table. In this case, table reference for correlated column should appear before derived table in “FROM” clause table list.
create table t1 (c1 int);
create table t2 (c2 int);
create table t3 (c3 int);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t2 values (1);
insert into t2 values (2);
insert into t3 values (1);
insert into t3 values (2);
select t1.c1, dc1 from t1, LATERAL (select t2.c2 from t2 where t1.c1 = t2.c2) as dtab(dc1) ;
2 row(s) retrieved.
In example-1, t1.c1 column is referenced inside derived table dtab. Since table t1 is not listed as part of the table list inside derived table, LATERAL keyword is required before derived table dtab. In this case, Informix server will provide t1.c1 value from t1 scan to the derived table dtab. Then t1 and dtab join operation is performed using output rows from dtab.
Since there is no value 3 in t2 table, above query returns 2 rows.
Note - Without LATERAL keyword, Informix server will return error for t1.c1 reference inside derived table.
select t1.c1, dc1 from t1 left join LATERAL (select t2.c2 from t2 where t1.c1 = t2.c2) as dtab(dc1) on 1=1 ;
3 row(s) retrieved.
Above Example-2 has left join instead of Informix join in Example-1. In this case, value 3 from t1 table is not matched with derived table column, which gives null value for dc1 column.
select t1.c1, dc1 from t1 left join LATERAL (select t2.c2 from t2 left join t3 on t2.c2 = t3.c3 where t1.c1 = 1) as dtab(dc1) on 1=1 ;
4 row(s) retrieved.
Example-3 has left join between t2 and t3 inside derived table and it has post-join filter with lateral column reference t1.c1. Table t1 scan will provide value 1 to the derived table dtab.Now derived table dtab returns 2 rows ( dc1 = 1 & dc1 = 2 ) from “left join” operation between t2 and t3. Remaining scan values from t1 does not produce any rows from derived table dtab.