Snowpark Migration Accelerator: Join¶

Description¶

Combines the rows from two table references based on join criteria. (Databricks SQL Language Reference JOIN)

A JOIN operation combines rows from two tables (or other table-like sources, such as views or table functions) to create a new combined row that can be used in the query. For a conceptual explanation of joins, see Working with Joins. (Snowflake SQL Language Reference JOIN)

Syntax¶

left_table_reference { [ join_type ] JOIN right_table_reference join_criteria |
           NATURAL join_type JOIN right_table_reference |
           CROSS JOIN right_table_reference }

join_type
  { [ INNER ] |
    LEFT [ OUTER ] |
    [ LEFT ] SEMI |
    RIGHT [ OUTER ] |
    FULL [ OUTER ] |
    [ LEFT ] ANTI |
    CROSS }

join_criteria
  { ON boolean_expression |
    USING ( column_name [, ...] ) }
Copy
SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]

SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]
Copy

Sample Source Patterns¶

Setup data¶

Databricks¶

-- Use employee and department tables to demonstrate different type of joins.
CREATE TEMP VIEW employee(id, name, deptno) AS
     VALUES(105, 'Chloe', 5),
           (103, 'Paul', 3),
           (101, 'John', 1),
           (102, 'Lisa', 2),
           (104, 'Evan', 4),
           (106, 'Amy', 6);

CREATE TEMP VIEW department(deptno, deptname) AS
    VALUES(3, 'Engineering'),
          (2, 'Sales'      ),
          (1, 'Marketing'  );
Copy

Snowflake¶

-- Use employee and department tables to demonstrate different type of joins.
CREATE TEMPORARY TABLE employee(id, name, deptno) AS
SELECT id, name, deptno
  FROM (VALUES (105, 'Chloe', 5),
           (103, 'Paul' , 3),
           (101, 'John' , 1),
           (102, 'Lisa' , 2),
           (104, 'Evan' , 4),
           (106, 'Amy'  , 6)) AS v1 (id, name, deptno);

CREATE TEMP VIEW department(deptno, deptname) AS
SELECT deptno, deptname
  FROM (VALUES(3, 'Engineering'),
          (2, 'Sales'      ),
          (1, 'Marketing'  )) AS v1 (deptno, deptname);
Copy

Pattern code¶

Databricks¶

-- 1. Use employee and department tables to demonstrate inner join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   INNER JOIN department ON employee.deptno = department.deptno;
 
-- 2. Use employee and department tables to demonstrate left join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   LEFT JOIN department ON employee.deptno = department.deptno;


-- 3. Use employee and department tables to demonstrate right join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    RIGHT JOIN department ON employee.deptno = department.deptno;


-- 4. Use employee and department tables to demonstrate full join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    FULL JOIN department ON employee.deptno = department.deptno;


-- 5. Use employee and department tables to demonstrate cross join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    CROSS JOIN department;

-- 6. Use employee and department tables to demonstrate semi join.
SELECT *
    FROM employee
    SEMI JOIN department ON employee.deptno = department.deptno;
Copy

  1. Use employee and department tables to demonstrate inner join.

id

name

deptno

deptname

103

Paul

3

Engineering

101

John

1

Marketing

102

Lisa

2

Sales

  1. Use employee and department tables to demonstrate left join.

id

name

deptno

deptname

105

Chloe

5

null

103

Paul

3

Engineering

101

John

1

Marketing

102

Lisa

2

Sales

104

Evan

4

null

106

Amy

6

null

  1. Use employee and department tables to demonstrate right join.

id

name

deptno

deptname

103

Paul

3

Engineering

102

Lisa

2

Sales

101

John

1

Marketing

  1. Use employee and department tables to demonstrate full join.

id

name

deptno

deptname

101

John

1

Marketing

102

Lisa

2

Sales

103

Paul

3

Engineering

104

Evan

4

null

105

Chloe

5

null

106

Amy

6

null

  1. Use employee and department tables to demonstrate cross join.

id

name

deptno

deptname

105

Chloe

5

Engineering

105

Chloe

5

Sales

105

Chloe

5

Marketing

103

Paul

3

Engineering

103

Paul

3

Sales

103

Paul

3

Marketing

101

John

1

Engineering

101

John

1

Sales

101

John

1

Marketing

102

Lisa

2

Engineering

102

Lisa

2

Sales

102

Lisa

2

Marketing

104

Evan

4

Engineering

104

Evan

4

Sales

104

Evan

4

Marketing

106

Amy

6

Engineering

106

Amy

6

Sales

106

Amy

6

Marketing

  1. Use employee and department tables to demonstrate semi join.

id

name

deptno

103

Paul

3

101

John

1

102

Lisa

2

Snowflake¶

-- 1. Use employee and department tables to demonstrate inner join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   INNER JOIN department ON employee.deptno = department.deptno;
 
-- 2. Use employee and department tables to demonstrate left join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   LEFT JOIN department ON employee.deptno = department.deptno;


-- 3. Use employee and department tables to demonstrate right join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    RIGHT JOIN department ON employee.deptno = department.deptno;


-- 4. Use employee and department tables to demonstrate full join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    FULL JOIN department ON employee.deptno = department.deptno;


-- 5. Use employee and department tables to demonstrate cross join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    CROSS JOIN department;

-- 6. Use employee and department tables to demonstrate semi join.
SELECT e.*
    FROM employee e, department d
    WHERE e.deptno = d.deptno;
Copy

  1. Use employee and department tables to demonstrate inner join.

id

name

deptno

deptname

103

Paul

3

Engineering

101

John

1

Marketing

102

Lisa

2

Sales

  1. Use employee and department tables to demonstrate left join.

id

name

deptno

deptname

105

Chloe

5

null

103

Paul

3

Engineering

101

John

1

Marketing

102

Lisa

2

Sales

104

Evan

4

null

106

Amy

6

null

  1. Use employee and department tables to demonstrate right join.

id

name

deptno

deptname

103

Paul

3

Engineering

102

Lisa

2

Sales

101

John

1

Marketing

  1. Use employee and department tables to demonstrate full join.

id

name

deptno

deptname

105

Chloe

5

null

103

Paul

3

Engineering

101

John

1

Marketing

102

Lisa

2

Sales

104

Evan

4

null

106

Amy

6

null

  1. Use employee and department tables to demonstrate cross join.

id

name

deptno

deptname

105

Chloe

5

Engineering

105

Chloe

5

Sales

105

Chloe

5

Marketing

103

Paul

3

Engineering

103

Paul

3

Sales

103

Paul

3

Marketing

101

John

1

Engineering

101

John

1

Sales

101

John

1

Marketing

102

Lisa

2

Engineering

102

Lisa

2

Sales

102

Lisa

2

Marketing

104

Evan

4

Engineering

104

Evan

4

Sales

104

Evan

4

Marketing

106

Amy

6

Engineering

106

Amy

6

Sales

106

Amy

6

Marketing

  1. Use employee and department tables to demonstrate semi join.

id

name

deptno

103

Paul

3

101

John

1

102

Lisa

2

Known Issues¶

No issues were found

Language: English