Snowpark Migration Accelerator： Join¶

描述¶

Merges rows from two table references (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-table-reference.html) using specified join conditions. For more details, see the Databricks SQL Language Reference JOIN (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-join.html).

A JOIN combines data from two sources (such as tables or views) into a single result set. Each row in the result contains columns from both sources based on a specified condition. For a detailed explanation of joins, see Working with Joins. (Snowflake SQL Language Reference JOIN)

语法¶

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 [, ...] ) }

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>
[ ... ]

示例源模式¶

设置数据¶

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'  );

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);

模式代码¶

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. We will use the employee and department tables to show how a left join works. This example will help you understand how to combine data from two tables while keeping all records from the left (first) table.
SELECT id, name, employee.deptno, deptname
   FROM employee
   LEFT JOIN department ON employee.deptno = department.deptno;

-- 3. Demonstrate a RIGHT JOIN using employee and department tables. This query retrieves all departments and matching employees.
SELECT id, name, employee.deptno, deptname
    FROM employee
    RIGHT JOIN department ON employee.deptno = department.deptno;

-- 4. Demonstrate a FULL JOIN operation using the employee and department tables.
SELECT id, name, employee.deptno, deptname
    FROM employee
    FULL JOIN department ON employee.deptno = department.deptno;

-- 5. Demonstrate a cross join operation using the employee and department tables. This query returns all possible combinations of employees and departments.
SELECT id, name, employee.deptno, deptname
    FROM employee
    CROSS JOIN department;

-- 6. This example shows how to use a semi join between employee and department tables. A semi join returns records from the first table (employee) where there is a matching record in the second table (department).
```{code} sql
SELECT *
    FROM employee
    SEMI JOIN department ON employee.deptno = department.deptno;

我们将以两个表（“员工表”和“部门表”）为例，演示内部联接的工作原理。内部联接合并了两个表中指定列之间存在匹配项的行。


id	name	deptno	deptname
103	Paul	3	Engineering
101	John	1	Marketing
102	Lisa	2	Sales

我们将以员工表和部门表为例，演示左联接的工作原理。此示例将帮助您了解如何合并两个表中的数据，同时保留左侧（第一个）表中的所有记录。


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

我们将以员工表和部门表为例，演示 RIGHT JOIN 在 SQL 中的运作方式。


id	name	deptno	deptname
103	Paul	3	Engineering
102	Lisa	2	Sales
101	John	1	Marketing

我们将以员工表和部门表为例，演示完全联接的工作原理。完全联接可合并两个表中的所有记录，包括任一表中不匹配的行。


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

在员工表和部门表之间创建交叉联接，以展示如何将一个表中的每一行与另一个表中的每一行合并。


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

我们将以员工表和部门表为例，演示半联接的工作原理。半联接返回在第二个表中有匹配记录的第一个表中的记录。


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;

我们将以两个表（“员工表”和“部门表”）为例，演示内部联接的工作原理。内部联接可合并两个表中指定列中存在匹配值的记录。


id	name	deptno	deptname
103	Paul	3	Engineering
101	John	1	Marketing
102	Lisa	2	Sales

使用员工表和部门表来演示左联接。


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

我们将以员工表和部门表为例，演示右联接的工作原理。


id	name	deptno	deptname
103	Paul	3	Engineering
102	Lisa	2	Sales
101	John	1	Marketing

我们将以员工表和部门表为例，演示完全联接的工作原理。完全联接可合并两个表中的所有记录，包括任一表中不匹配的行。


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

在员工表和部门表之间创建交叉连接，以展示如何将每位员工与每个部门配对。此示例通过合并两个表中所有可能的行组合来演示交叉联接的工作原理。


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

我们将以员工表和部门表为例，演示半联接的工作原理。半联接返回在第二个表中有匹配记录的第一个表中的记录。


id	name	deptno
103	Paul	3
101	John	1
102	Lisa	2

已知问题¶

未发现任何问题

Snowpark Migration Accelerator： Join¶

描述¶

语法¶

示例源模式¶

设置数据¶

Databricks¶

Snowflake¶

模式代码¶

Databricks¶

Snowflake¶

已知问题¶

相关的 EWIs¶