Joined Table

Description

A joined table specifies an intermediate result table that is the result of either an inner join or an outer join. The table is derived by applying one of the join operators: CROSS, INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER to its operands.

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

Grammar Syntax

All the DB2 Joined table grammar is supported by Snowflake and is therefore migrated as-is by SnowConvert because the results will be the same.

Sample Source Patterns

In order to test the JOIN, first we need some tables with some data.

Note that the following samples can be done in both IBM DB2 and Snowflake.

Tables with Insert

create table t1 (col1 integer);
create table t2 (col1 integer);
insert into t1 (col1) values 
   (2),
   (3),
   (4);
insert into t2 (col1) values 
   (1),
   (2),
   (2),
   (3);

Now that we have some tables, we can do some examples with joins.

Sample 1

   select t1.col1, t2.col1
    from t1 inner join t2
        on t2.col1 = t1.col1
    order by 1,2;

DB2

Snowflake

Sample 2

    select t1.col1, t2.col1
    from t1 left outer join t2
        on t2.col1 = t1.col1
    order by 1,2;

DB2

Snowflake

Sample 3

    select t1.col1, t2.col1
    from t1 right outer join t2
        on t2.col1 = t1.col1
    order by 1,2;

DB2

Snowflake

Sample 4

    select t1.col1, t2.col1
    from t1 full outer join t2
        on t2.col1 = t1.col1
    order by 1,2;

DB2

Snowflake

Sample 5

    select t1.col1, t2.col1
    from t1 cross join t2
    order by 1, 2;

IBM

Snowflake