Snowpark Migration Accelerator: Join¶

Descrição¶

Merges rows from two table references using specified join conditions. For more details, see the Databricks SQL Language Reference JOIN.

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)

Sintaxe¶

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

Amostra de padrões da origem¶

Dados de configuração¶

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

Código do padrão¶

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;

Usaremos duas tabelas de amostra - «employee» e «department» - para mostrar como funciona uma junção interna. Uma junção interna combina linhas de ambas as tabelas em que há uma correspondência entre as colunas especificadas.


id	name	deptno	deptname
103	Paul	3	Engenharia
101	John	1	Marketing
102	Lisa	2	Vendas

Usaremos as tabelas employee e department para mostrar como funciona uma junção esquerda. Este exemplo ajudará a entender como combinar dados de duas tabelas, mantendo todos os registros da tabela da esquerda (primeira).


id	name	deptno	deptname
105	Chloe	5	nulo
103	Paul	3	Engenharia
101	John	1	Marketing
102	Lisa	2	Vendas
104	Evan	4	nulo
106	Amy	6	nulo

Vamos usar as tabelas employee e department para mostrar como RIGHT JOIN funciona em SQL.


id	name	deptno	deptname
103	Paul	3	Engenharia
102	Lisa	2	Vendas
101	John	1	Marketing

Vamos usar as tabelas employee e department para mostrar como funciona uma junção completa. Uma junção completa combina todos os registros de ambas as tabelas, inclusive as linhas não correspondentes de qualquer uma delas.


id	name	deptno	deptname
101	John	1	Marketing
102	Lisa	2	Vendas
103	Paul	3	Engenharia
104	Evan	4	nulo
105	Chloe	5	nulo
106	Amy	6	nulo

Crie uma junção cruzada entre as tabelas employee e department para mostrar como combinar cada linha de uma tabela com cada linha de outra tabela.


id	name	deptno	deptname
105	Chloe	5	Engenharia
105	Chloe	5	Vendas
105	Chloe	5	Marketing
103	Paul	3	Engenharia
103	Paul	3	Vendas
103	Paul	3	Marketing
101	John	1	Engenharia
101	John	1	Vendas
101	John	1	Marketing
102	Lisa	2	Engenharia
102	Lisa	2	Vendas
102	Lisa	2	Marketing
104	Evan	4	Engenharia
104	Evan	4	Vendas
104	Evan	4	Marketing
106	Amy	6	Engenharia
106	Amy	6	Vendas
106	Amy	6	Marketing

Vamos usar as tabelas employee e department para mostrar como funciona uma semijunção. Uma semijunção retorna registros da primeira tabela quando há um registro correspondente na segunda tabela.


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;

Usaremos duas tabelas de amostra - «employee» e «department» - para mostrar como funciona uma junção interna. Uma junção interna combina registros de ambas as tabelas em que há um valor correspondente nas colunas especificadas.


id	name	deptno	deptname
103	Paul	3	Engenharia
101	John	1	Marketing
102	Lisa	2	Vendas

Use as tabelas employee e department para demonstrar a junção esquerda.


id	name	deptno	deptname
105	Chloe	5	nulo
103	Paul	3	Engenharia
101	John	1	Marketing
102	Lisa	2	Vendas
104	Evan	4	nulo
106	Amy	6	nulo

Vamos usar as tabelas employee e department para mostrar como funciona uma junção direita.


id	name	deptno	deptname
103	Paul	3	Engenharia
102	Lisa	2	Vendas
101	John	1	Marketing

Vamos usar as tabelas employee e department para mostrar como funciona uma junção completa. Uma junção completa combina todos os registros de ambas as tabelas, inclusive as linhas não correspondentes de qualquer uma delas.


id	name	deptno	deptname
105	Chloe	5	nulo
103	Paul	3	Engenharia
101	John	1	Marketing
102	Lisa	2	Vendas
104	Evan	4	nulo
106	Amy	6	nulo

Crie uma junção cruzada entre as tabelas employee e department para mostrar como cada funcionário pode ser associado a cada departamento. Este exemplo demonstra como as junções cruzadas funcionam, com todas as combinações possíveis de linhas de ambas as tabelas.


id	name	deptno	deptname
105	Chloe	5	Engenharia
105	Chloe	5	Vendas
105	Chloe	5	Marketing
103	Paul	3	Engenharia
103	Paul	3	Vendas
103	Paul	3	Marketing
101	John	1	Engenharia
101	John	1	Vendas
101	John	1	Marketing
102	Lisa	2	Engenharia
102	Lisa	2	Vendas
102	Lisa	2	Marketing
104	Evan	4	Engenharia
104	Evan	4	Vendas
104	Evan	4	Marketing
106	Amy	6	Engenharia
106	Amy	6	Vendas
106	Amy	6	Marketing

Vamos usar as tabelas employee e department para mostrar como funciona uma semijunção. Uma semijunção retorna registros da primeira tabela quando há um registro correspondente na segunda tabela.


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

Problemas conhecidos¶

Não foram encontrados problemas

Snowpark Migration Accelerator: Join¶

Descrição¶

Sintaxe¶

Amostra de padrões da origem¶

Dados de configuração¶

Databricks¶

Snowflake¶

Código do padrão¶

Databricks¶

Snowflake¶

Problemas conhecidos¶

EWIs relacionados¶