Literal and columnar expression arguments

When you use a combination of literal and column expression arguments, you invoke a correlated table function.

Note: A correlated subquery (also known as a repeating subquery) is a special table, row, or scalar subquery that includes a reference to an outer table. Do not use correlated subqueries because they can be slow; they evaluate the subquery once per input row of the outer table. There is limited support for correlated subqueries.

A lateral subquery is a special form of correlated subquery that appears in a FROM clause and includes a reference to an outer table that appears earlier the same FROM clause. The LATERAL keyword typically identifies this type of correlation, although the TABLE keyword can be used as well. Netezza Performance Server does not support the LATERAL keyword and supports the TABLE keyword only for UDTFs (not for SQL subqueries).

The tables that contain the columns that are referenced by the table function must appear before the table function invocation in the SQL query. A table function is laterally correlated with the tables whose columns it uses, even when the invocation appears to be that of a JOIN operation. There are two forms of lateral correlations for UDTFs, inner and left outer correlation:

With inner correlation, the table function is invoked once for each input row. The table output contains all of the output rows that are produced for that input row plus the corresponding input row. If the table function does not produce an output row for an input row, the input row is omitted from the table output. Also, if the table function produces an output row for an input row, but the join qualifier evaluates to false, the input row is omitted from the combined output. If the UDTF can be called by using the TABLE WITH FINAL syntax, there might be more output rows as a result of the WITH FINAL processing. Two examples follow:
```
mydb.schema(usr1)=> SELECT t.cust_id, f.product_id FROM orders AS t, TABLE (
parseNames(prod_codes) ) AS f WHERE t.cust_id='AB123456';
 cust_id  | product_id
----------+------------
 AB123456 | 124
 AB123456 | 6
 AB123456 | 12
 AB123456 | 121
 AB123456 | 87
 AB123456 | 182
(6 rows)

mydb.schema(usr1)=> SELECT t.cust_id, f.product_id FROM orders AS t, TABLE (
parseNames(prod_codes) ) AS f WHERE t.cust_id='AB223456';
 cust_id  | product_id
----------+------------
(0 rows)
```

With left outer correlation, the major difference is that in cases where the input row does not produce output or in cases where the join qualifier evaluates to false, the UDTF displays the result of the table function with NULL values in its columns. For example:

mydb.schema(usr1)=> SELECT * FROM orders AS f LEFT OUTER JOIN
TABLE(parsenames(f.prod_codes)) ON product_id='121';
 ORDER_ID | CUST_ID  | SALE_DATE  |      PROD_CODES       | PRODUCT_ID
----------+----------+------------+-----------------------+------------
      150 | CD876543 | 2010-09-05 | 80,43,55,12,4,67,92   |
      142 | AB664353 | 2010-09-04 | 1,145,52,53,93,98,100 |
      124 | AB123456 | 2010-08-26 | 124,6,12,121          | 121
      143 | AB123456 | 2010-09-05 | 87,182                |
      120 | AB876123 | 2010-09-05 | 28,36,80              |
      131 | AB643623 | 2010-09-02 | 12,88,41              |
      132 | AB643623 | 2010-09-04 | 121                   | 121
      125 | AB987657 | 2010-08-26 | 8                     |
      126 | AB456754 | 2010-09-01 | 32,5,76,65,121,98     | 121
(9 rows)

A laterally correlated table function has the following additional restrictions:

It cannot occur in a RIGHT OUTER JOIN where it is laterally correlated to the table that is being joined on.
It cannot occur in a FULL OUTER JOIN where it is laterally correlated to the table that is being joined on.
When used in a LEFT OUTER or INNER JOIN, where it is laterally correlated to the table in the join clause, you get correlation behavior and not join behavior.