Nested Table Expression

Description

A fullselect in parentheses is called a nested table expression. The intermediate result table is the result of that fullselect.

Click here to navigate to the IBM DB2 docs page for this syntax.

Nested Table Expression is partially applicable in Snowflake.

Grammar Syntax

Sample Source Patterns

Supported cases

IBM DB2

Code 1

select empno, firstnme, lastname
      from
         (select * from employee where workdept = 'D21');

Result

EMPNO	FIRSTNME	LASTNAME
000070	EVA	PULASKI
000230	JAMES	JEFFERSON
000240	SALVATORE	MARINO
000250	DANIEL	SMITH
000260	SYBIL	JOHNSON
000270	MARIA	PEREZ
200240	ROBERT	MONTEVERDE

Snowflake

Code 1

select empno, firstnme, lastname
      from
         (select * from employee where workdept = 'D21');

Result

EMPNO	FIRSTNME	LASTNAME
000070	EVA	PULASKI
000230	JAMES	JEFFERSON
000240	SALVATORE	MARINO
000250	DANIEL	SMITH
000260	SYBIL	JOHNSON
000270	MARIA	PEREZ
200240	ROBERT	MONTEVERDE

IBM DB2

Code 1

select
   deptno,
   empno
from
   department as d,
   lateral(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

DEPTNO	EMPNO
D21	000070
D21	000230
D21	000240
D21	000250
D21	000260
D21	000270
D21	200240

Snowflake

Code 1

select
   deptno,
   empno
from
   department as d,
   LATERAL(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

DEPTNO	EMPNO
D21	000070
D21	000230
D21	000240
D21	000250
D21	000260
D21	000270
D21	200240

IBM DB2

Code 1

select
   deptno,
   empno
from
   department as d,
   table(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

DEPTNO	EMPNO
D21	000070
D21	000230
D21	000240
D21	000250
D21	000260
D21	000270
D21	200240

Snowflake

Code 1

select
   deptno,
   empno
from
   department as d,
   LATERAL(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

DEPTNO	EMPNO
D21	000070
D21	000230
D21	000240
D21	000250
D21	000260
D21	000270
D21	200240

Unsupported cases

IBM DB2

Code 1

Select
   AValue
from
   LATERAL RETURN DATA UNTIL FEDERATED SQLSTATE VALUE 'stringConstant' WITHIN(
      Select
         AValue
      from
         ATable
   );

Snowflake

Code 1

Select
   AValue
from
   LATERAL
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONTINUE HANDLER **
--           RETURN DATA UNTIL FEDERATED SQLSTATE VALUE 'stringConstant' WITHIN
                                                                             (
      Select
         AValue
      from
         ATable
   );

Related EWIs

1. MSCEWI1002: REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.