SnowConvert: Junções do Oracle¶
Uma junção é uma consulta que combina linhas de duas ou mais tabelas, exibições ou exibições materializadas. O Oracle Database realiza uma junção sempre que várias tabelas aparecem na cláusula FROM da consulta. (Referência da linguagem Oracle SQL JOINS)
Veja a seguir os artigos dessa seção:
Antijoin¶
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Descrição¶
Um antijoin retorna linhas do lado esquerdo do predicado para as quais não há linhas correspondentes no lado direito do predicado. Ele retorna as linhas que não correspondem (NOT IN) à subconsulta no lado direito. A transformação antijoin não pode ser feita se a subconsulta estiver em um ramo OR da cláusula WHERE. (Referência da linguagem Oracle SQL Antijoint).
Nenhuma transformação especial é realizada para esse tipo de Join, pois o Snowflake suporta a mesma sintaxe.
Amostra de padrões da origem¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Como o conjunto de resultados é muito grande, foi adicionada a cláusula Row Limiting. Você pode removê-lo para recuperar todo o conjunto de resultados.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Where Not em¶
Oracle¶
SELECT e.employee_id, e.first_name, e.last_name FROM hr.employees e
WHERE e.department_id NOT IN
(SELECT h.department_id FROM hr.departments h WHERE location_id = 1700)
ORDER BY e.last_name
FETCH FIRST 10 ROWS ONLY;
EMPLOYEE_ID|FIRST_NAME|LAST_NAME|
-----------+----------+---------+
174|Ellen |Abel |
166|Sundar |Ande |
130|Mozhe |Atkinson |
105|David |Austin |
204|Hermann |Baer |
167|Amit |Banda |
172|Elizabeth |Bates |
192|Sarah |Bell |
151|David |Bernstein|
129|Laura |Bissot |
Snowflake¶
SELECT e.employee_id, e.first_name, e.last_name FROM
hr.employees e
WHERE e.department_id NOT IN
!!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!!
(SELECT h.department_id FROM
hr.departments h WHERE location_id = 1700)
ORDER BY e.last_name
FETCH FIRST 10 ROWS ONLY;
EMPLOYEE_ID|FIRST_NAME|LAST_NAME|
-----------+----------+---------+
174|Ellen |Abel |
166|Sundar |Ande |
130|Mozhe |Atkinson |
105|David |Austin |
204|Hermann |Baer |
167|Amit |Banda |
172|Elizabeth |Bates |
192|Sarah |Bell |
151|David |Bernstein|
129|Laura |Bissot |
Where Not existe¶
Oracle¶
SELECT d.department_id, d.department_name
FROM hr.departments d
WHERE NOT EXISTS
(SELECT 1 FROM hr.employees E WHERE
e.department_id = d.department_id)
ORDER BY d.department_id
FETCH FIRST 10 ROWS ONLY;
DEPARTMENT_ID|DEPARTMENT_NAME |
-------------+--------------------+
120|Treasury |
130|Corporate Tax |
140|Control And Credit |
150|Shareholder Services|
160|Benefits |
170|Manufacturing |
180|Construction |
190|Contracting |
200|Operations |
210|IT Support |
Snowflake¶
SELECT d.department_id, d.department_name
FROM
hr.departments d
WHERE NOT EXISTS
!!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!!
(SELECT 1 FROM
hr.employees E WHERE
e.department_id = d.department_id)
ORDER BY d.department_id
FETCH FIRST 10 ROWS ONLY;
DEPARTMENT_ID|DEPARTMENT_NAME |
-------------+--------------------+
120|Treasury |
130|Corporate Tax |
140|Control And Credit |
150|Shareholder Services|
160|Benefits |
170|Manufacturing |
180|Construction |
190|Contracting |
200|Operations |
210|IT Support |
Problemas conhecidos¶
1. Results ordering mismatch between languages¶
O resultado da consulta terá o mesmo conteúdo em ambos os mecanismos de banco de dados, mas a ordem poderá ser diferente se nenhuma cláusula Order By for definida na consulta.
Band Join¶
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Descrição¶
Uma band join é um tipo especial de nonequijoin em que os valores-chave em um conjunto de dados devem estar dentro do intervalo especificado («banda») do segundo conjunto de dados. A mesma tabela pode servir tanto para o primeiro quanto para o segundo conjunto de dados. (Referência da linguagem Oracle SQL BandJoin)
Nesta seção, veremos como uma band join é executada no Snowflake, e o plano de execução é muito semelhante à versão aprimorada do Oracle.
Amostra de padrões da origem¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Como o conjunto de resultados é muito grande, foi adicionada a cláusula Row Limiting. Você pode removê-lo para recuperar todo o conjunto de resultados.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Aviso
Se você migrar esse código sem criar tabelas, o conversor não conseguirá carregar as informações semânticas das colunas e será exibido um aviso sobre as operações aritméticas.
Caso básico de Band Join¶
Oracle¶
SELECT e1.last_name ||
' has salary between 100 less and 100 more than ' ||
e2.last_name AS "SALARY COMPARISON"
FROM employees e1,
employees e2
WHERE e1.salary
BETWEEN e2.salary - 100
AND e2.salary + 100
ORDER BY "SALARY COMPARISON"
FETCH FIRST 10 ROWS ONLY
SALARY COMPARISON |
---------------------------------------------------------------+
Abel has salary between 100 less and 100 more than Abel |
Abel has salary between 100 less and 100 more than Cambrault |
Abel has salary between 100 less and 100 more than Raphaely |
Ande has salary between 100 less and 100 more than Ande |
Ande has salary between 100 less and 100 more than Mavris |
Ande has salary between 100 less and 100 more than Vollman |
Atkinson has salary between 100 less and 100 more than Atkinson|
Atkinson has salary between 100 less and 100 more than Baida |
Atkinson has salary between 100 less and 100 more than Gates |
Atkinson has salary between 100 less and 100 more than Geoni |
Snowflake¶
SELECT
NVL( e1.last_name :: STRING, '') ||
' has salary between 100 less and 100 more than ' || NVL(
e2.last_name :: STRING, '') AS "SALARY COMPARISON"
FROM
employees e1,
employees e2
WHERE e1.salary
BETWEEN
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! e2.salary - 100
AND
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! e2.salary + 100
ORDER BY "SALARY COMPARISON"
FETCH FIRST 10 ROWS ONLY;
SALARY COMPARISON |
---------------------------------------------------------------+
Abel has salary between 100 less and 100 more than Abel |
Abel has salary between 100 less and 100 more than Cambrault |
Abel has salary between 100 less and 100 more than Raphaely |
Ande has salary between 100 less and 100 more than Ande |
Ande has salary between 100 less and 100 more than Mavris |
Ande has salary between 100 less and 100 more than Vollman |
Atkinson has salary between 100 less and 100 more than Atkinson|
Atkinson has salary between 100 less and 100 more than Baida |
Atkinson has salary between 100 less and 100 more than Gates |
Atkinson has salary between 100 less and 100 more than Geoni |
Aviso
A migração de algumas instruções SELECT sem as tabelas correspondentes pode gerar SSC-EWI-OR0036: Problemas de resolução de tipos. Para evitar esse aviso, inclua CREATE TABLE dentro do arquivo.
Os resultados são os mesmos, tornando BAND JOIN funcional equivalente.
Plano de execução
Como informação extra, o que há de especial nas junções de bandas é o plano de execução.
A imagem a seguir mostra o plano de execução aprimorado (implementado desde o Oracle 12c) para a consulta de teste:
(1).png)
E na imagem a seguir, veremos o plano de execução no Snowflake:
(1).png)
Nota
O plano de execução no Snowflake é muito semelhante à versão otimizada do Oracle. A duração final e o desempenho da consulta serão afetados por muitos outros fatores e dependem totalmente de cada funcionalidade interna do DBMS.
Problemas conhecidos¶
1. Results ordering mismatch between languages¶
O resultado da consulta terá o mesmo conteúdo em ambos os mecanismos de banco de dados, mas a ordem poderá ser diferente se nenhuma cláusula Order By for definida na consulta.
EWIs relacionados¶
SSC-EWI-OR0036: Problemas de resolução de tipos, a operação aritmética pode não se comportar corretamente entre string e data.
Produtos cartesianos¶
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Se duas tabelas em uma consulta de junção não tiverem condição de junção, o Oracle Database retornará o produto cartesiano delas. O Oracle combina cada linha de uma tabela com cada linha da outra. (Subseção Cartesian Products da referência da linguagem Oracle SQL)
O Oracle e o Snowflake também são compatíveis com a sintaxe ANSI de Cross Join, que tem o mesmo comportamento de um produto cartesiano.
Nenhuma transformação especial é realizada para esse tipo de Join, pois o Snowflake suporta a mesma sintaxe.
Amostra de padrões da origem¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Como o conjunto de resultados é muito grande, foi adicionada a cláusula Row Limiting. Você pode removê-lo para recuperar todo o conjunto de resultados.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Sintaxe implícita¶
Oracle¶
-- Resulting rows
SELECT * FROM hr.employees, hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM hr.employees, hr.departments;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID|SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|------|------------|-----------------------|------|------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |10 |Administration |200 |1700 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |50 |Shipping |121 |1500 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |40 |Human Resources|203 |2400 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |30 |Purchasing |114 |1700 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |20 |Marketing |201 |1800 |
|COUNT(*)|
|--------|
|2889 |
Snowflake¶
-- Resulting rows
SELECT * FROM
hr.employees,
hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM
hr.employees,
hr.departments;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID|SALARY |COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|------|------------|----------|------|-------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00| |100 |50 |40 |Human Resources|203 |2400 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00| |100 |50 |20 |Marketing |201 |1800 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00| |100 |50 |10 |Administration |200 |1700 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00| |100 |50 |50 |Shipping |121 |1500 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00| |100 |50 |30 |Purchasing |114 |1700 |
|COUNT(*)|
|--------|
|2889 |
Sintaxe de Cross Join¶
Oracle¶
-- Resulting rows
SELECT * FROM hr.employees CROSS join hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM hr.employees CROSS join hr.departments;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID|SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|------|------------|-----------------------|------|------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |10 |Administration |200 |1700 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |50 |Shipping |121 |1500 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |40 |Human Resources|203 |2400 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |30 |Purchasing |114 |1700 |
|121 |Adam |Fripp |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200 | |100 |50 |20 |Marketing |201 |1800 |
|COUNT(*)|
|--------|
|2889 |
Snowflake¶
-- Resulting rows
SELECT * FROM
hr.employees
CROSS join hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM
hr.employees
CROSS join hr.departments;
Problemas conhecidos¶
1. Results ordering mismatch between languages¶
O resultado da consulta terá o mesmo conteúdo em ambos os mecanismos de banco de dados, mas a ordem poderá ser diferente se nenhuma cláusula Order By for definida na consulta.
EWIs relacionados¶
Sem EWIs relacionados.
Equijoin¶
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Descrição¶
Um equijoin é uma forma implícita de junção com uma condição de junção que contém um operador de igualdade. Para obter mais informações sobre o Oracle Equijoin, veja aqui.
Nenhuma transformação especial é realizada para esse tipo de Join, pois o Snowflake suporta a mesma sintaxe.
Amostra de padrões da origem¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Como o conjunto de resultados é muito grande, foi adicionada a Row Limiting Clause. Você pode removê-lo para recuperar todo o conjunto de resultados.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Caso básico de Equijoin¶
Oracle¶
SELECT last_name, job_id, hr.departments.department_id, department_name
FROM hr.employees, hr.departments
WHERE hr.employees.department_id = hr.departments.department_id
ORDER BY last_name
FETCH FIRST 5 ROWS ONLY;
|LAST_NAME|JOB_ID |DEPARTMENT_ID|DEPARTMENT_NAME |
|---------|--------|-------------|----------------|
|Abel |SA_REP |80 |Sales |
|Ande |SA_REP |80 |Sales |
|Atkinson |ST_CLERK|50 |Shipping |
|Austin |IT_PROG |60 |IT |
|Baer |PR_REP |70 |Public Relations|
Snowflake¶
SELECT last_name, job_id, hr.departments.department_id, department_name
FROM
hr.employees,
hr.departments
WHERE hr.employees.department_id = hr.departments.department_id
ORDER BY last_name
FETCH FIRST 5 ROWS ONLY;
|LAST_NAME|JOB_ID |DEPARTMENT_ID|DEPARTMENT_NAME |
|---------|--------|-------------|----------------|
|Abel |SA_REP |80 |Sales |
|Ande |SA_REP |80 |Sales |
|Atkinson |ST_CLERK|50 |Shipping |
|Austin |IT_PROG |60 |IT |
|Baer |PR_REP |70 |Public Relations|
Problemas conhecidos¶
1. Results ordering mismatch between languages¶
O resultado da consulta terá o mesmo conteúdo em ambos os mecanismos de banco de dados, mas a ordem poderá ser diferente se nenhuma cláusula Order By for definida na consulta.
EWIs relacionados¶
Sem EWIs relacionados.
Junção interna¶
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Descrição¶
Uma junção interna (às vezes chamada de junção simples) é uma junção de duas ou mais tabelas que retorna apenas as linhas que satisfazem a condição de junção. (Subseção Inner Join da referência do Oracle SQL).
{ [ INNER ] JOIN table_reference
{ ON condition
| USING (column [, column ]...)
}
| { CROSS
| NATURAL [ INNER ]
}
JOIN table_reference
}
Amostra de padrões da origem¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Como o conjunto de resultados é muito grande, foi adicionada a cláusula Row Limiting. Você pode remover essa cláusula para recuperar todo o conjunto de resultados.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Inner Join básico¶
Na cláusula Inner Join «INNER» é uma palavra-chave opcional, as consultas a seguir têm dois selects que recuperam o mesmo conjunto de dados.
Oracle¶
SELECT
*
FROM
hr.employees
INNER JOIN hr.departments ON
hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
SELECT
*
FROM
hr.employees
JOIN hr.departments ON
hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|--------|------------|-----------------------|----------|------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|100 |Steven |King |SKING |515.123.4567|2003-06-17 00:00:00.000|AD_PRES |24000 | | |90 |90 |Executive |100 |1700 |
|101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21 00:00:00.000|AD_VP |17000 | |100 |90 |90 |Executive |100 |1700 |
|102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13 00:00:00.000|AD_VP |17000 | |100 |90 |90 |Executive |100 |1700 |
|103 |Alexander |Hunold |AHUNOLD |590.423.4567|2006-01-03 00:00:00.000|IT_PROG |9000 | |102 |60 |60 |IT |103 |1400 |
|104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21 00:00:00.000|IT_PROG |6000 | |103 |60 |60 |IT |103 |1400 |
|105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25 00:00:00.000|IT_PROG |4800 | |103 |60 |60 |IT |103 |1400 |
|106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05 00:00:00.000|IT_PROG |4800 | |103 |60 |60 |IT |103 |1400 |
|107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07 00:00:00.000|IT_PROG |4200 | |103 |60 |60 |IT |103 |1400 |
|108 |Nancy |Greenberg|NGREENBE|515.124.4569|2002-08-17 00:00:00.000|FI_MGR |12008 | |101 |100 |100 |Finance |108 |1700 |
|109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16 00:00:00.000|FI_ACCOUNT|9000 | |108 |100 |100 |Finance |108 |1700 |
Snowflake¶
SELECT
*
FROM
hr.employees
INNER JOIN
hr.departments
ON
hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
SELECT
*
FROM
hr.employees
JOIN
hr.departments
ON
hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY |COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|--------|------------|----------|----------|--------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|100 |Steven |King |SKING |515.123.4567|2003-06-17|AD_PRES |24000.00| | |90 |90 |Executive |100 |1700 |
|101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21|AD_VP |17000.00| |100 |90 |90 |Executive |100 |1700 |
|102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13|AD_VP |17000.00| |100 |90 |90 |Executive |100 |1700 |
|103 |Alexander |Hunold |AHUNOLD |590.423.4567|2006-01-03|IT_PROG |9000.00 | |102 |60 |60 |IT |103 |1400 |
|104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21|IT_PROG |6000.00 | |103 |60 |60 |IT |103 |1400 |
|105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25|IT_PROG |4800.00 | |103 |60 |60 |IT |103 |1400 |
|106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05|IT_PROG |4800.00 | |103 |60 |60 |IT |103 |1400 |
|107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07|IT_PROG |4200.00 | |103 |60 |60 |IT |103 |1400 |
|108 |Nancy |Greenberg|NGREENBE|515.124.4569|2002-08-17|FI_MGR |12008.00| |101 |100 |100 |Finance |108 |1700 |
|109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16|FI_ACCOUNT|9000.00 | |108 |100 |100 |Finance |108 |1700 |
Inner Join com cláusula using¶
Oracle¶
SELECT
*
FROM
hr.employees
INNER JOIN hr.departments
USING(department_id)
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|DEPARTMENT_ID|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-------------|-----------|----------|---------|--------|------------|-----------------------|----------|------|--------------|----------|---------------|----------|-----------|
|90 |100 |Steven |King |SKING |515.123.4567|2003-06-17 00:00:00.000|AD_PRES |24000 | | |Executive |100 |1700 |
|90 |101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21 00:00:00.000|AD_VP |17000 | |100 |Executive |100 |1700 |
|90 |102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13 00:00:00.000|AD_VP |17000 | |100 |Executive |100 |1700 |
|60 |103 |Alexander |Hunold |AHUNOLD |590.423.4567|2006-01-03 00:00:00.000|IT_PROG |9000 | |102 |IT |103 |1400 |
|60 |104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21 00:00:00.000|IT_PROG |6000 | |103 |IT |103 |1400 |
|60 |105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25 00:00:00.000|IT_PROG |4800 | |103 |IT |103 |1400 |
|60 |106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05 00:00:00.000|IT_PROG |4800 | |103 |IT |103 |1400 |
|60 |107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07 00:00:00.000|IT_PROG |4200 | |103 |IT |103 |1400 |
|100 |108 |Nancy |Greenberg|NGREENBE|515.124.4569|2002-08-17 00:00:00.000|FI_MGR |12008 | |101 |Finance |108 |1700 |
|100 |109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16 00:00:00.000|FI_ACCOUNT|9000 | |108 |Finance |108 |1700 |
Snowflake¶
SELECT
*
FROM
hr.employees
INNER JOIN
hr.departments
USING(department_id)
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|DEPARTMENT_ID|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY |COMMISSION_PCT|MANAGER_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-------------|-----------|----------|---------|--------|------------|----------|----------|--------|--------------|----------|---------------|----------|-----------|
|90 |100 |Steven |King |SKING |515.123.4567|2003-06-17|AD_PRES |24000.00| | |Executive |100 |1700 |
|90 |101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21|AD_VP |17000.00| |100 |Executive |100 |1700 |
|90 |102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13|AD_VP |17000.00| |100 |Executive |100 |1700 |
|60 |103 |Alexander |Hunold |AHUNOLD |590.423.4567|2006-01-03|IT_PROG |9000.00 | |102 |IT |103 |1400 |
|60 |104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21|IT_PROG |6000.00 | |103 |IT |103 |1400 |
|60 |105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25|IT_PROG |4800.00 | |103 |IT |103 |1400 |
|60 |106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05|IT_PROG |4800.00 | |103 |IT |103 |1400 |
|60 |107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07|IT_PROG |4200.00 | |103 |IT |103 |1400 |
|100 |108 |Nancy |Greenberg|NGREENBE|515.124.4569|2002-08-17|FI_MGR |12008.00| |101 |Finance |108 |1700 |
|100 |109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16|FI_ACCOUNT|9000.00 | |108 |Finance |108 |1700 |
Cross Inner Join¶
Oracle¶
SELECT
*
FROM
hr.employees
CROSS JOIN hr.departments
ORDER BY department_name, employee_id
FETCH NEXT 10 ROWS ONLY;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|--------|------------|-----------------------|----------|------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|100 |Steven |King |SKING |515.123.4567|2003-06-17 00:00:00.000|AD_PRES |24000 | | |90 |110 |Accounting |205 |1700 |
|101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21 00:00:00.000|AD_VP |17000 | |100 |90 |110 |Accounting |205 |1700 |
|102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13 00:00:00.000|AD_VP |17000 | |100 |90 |110 |Accounting |205 |1700 |
|103 |Alexander |Hunold |AHUNOLD |590.423.4567|2006-01-03 00:00:00.000|IT_PROG |9000 | |102 |60 |110 |Accounting |205 |1700 |
|104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21 00:00:00.000|IT_PROG |6000 | |103 |60 |110 |Accounting |205 |1700 |
|105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25 00:00:00.000|IT_PROG |4800 | |103 |60 |110 |Accounting |205 |1700 |
|106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05 00:00:00.000|IT_PROG |4800 | |103 |60 |110 |Accounting |205 |1700 |
|107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07 00:00:00.000|IT_PROG |4200 | |103 |60 |110 |Accounting |205 |1700 |
|108 |Nancy |Greenberg|NGREENBE|515.124.4569|2002-08-17 00:00:00.000|FI_MGR |12008 | |101 |100 |110 |Accounting |205 |1700 |
|109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16 00:00:00.000|FI_ACCOUNT|9000 | |108 |100 |110 |Accounting |205 |1700 |
Snowflake¶
SELECT
*
FROM
hr.employees
CROSS JOIN hr.departments
ORDER BY department_name, employee_id
FETCH NEXT 10 ROWS ONLY;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY |COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|--------|------------|----------|----------|--------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|100 |Steven |King |SKING |515.123.4567|2003-06-17|AD_PRES |24000.00| | |90 |110 |Accounting |205 |1700 |
|101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21|AD_VP |17000.00| |100 |90 |110 |Accounting |205 |1700 |
|102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13|AD_VP |17000.00| |100 |90 |110 |Accounting |205 |1700 |
|103 |Alexander |Hunold |AHUNOLD |590.423.4567|2006-01-03|IT_PROG |9000.00 | |102 |60 |110 |Accounting |205 |1700 |
|104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21|IT_PROG |6000.00 | |103 |60 |110 |Accounting |205 |1700 |
|105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25|IT_PROG |4800.00 | |103 |60 |110 |Accounting |205 |1700 |
|106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05|IT_PROG |4800.00 | |103 |60 |110 |Accounting |205 |1700 |
|107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07|IT_PROG |4200.00 | |103 |60 |110 |Accounting |205 |1700 |
|108 |Nancy |Greenberg|NGREENBE|515.124.4569|2002-08-17|FI_MGR |12008.00| |101 |100 |110 |Accounting |205 |1700 |
|109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16|FI_ACCOUNT|9000.00 | |108 |100 |110 |Accounting |205 |1700 |
Natural Inner Join¶
Oracle¶
SELECT
*
FROM
hr.employees
NATURAL JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|MANAGER_ID|DEPARTMENT_ID|EMPLOYEE_ID|FIRST_NAME |LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY|COMMISSION_PCT|DEPARTMENT_NAME|LOCATION_ID|
|----------|-------------|-----------|-----------|---------|--------|------------|-----------------------|----------|------|--------------|---------------|-----------|
|100 |90 |101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21 00:00:00.000|AD_VP |17000 | |Executive |1700 |
|100 |90 |102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13 00:00:00.000|AD_VP |17000 | |Executive |1700 |
|103 |60 |104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21 00:00:00.000|IT_PROG |6000 | |IT |1400 |
|103 |60 |105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25 00:00:00.000|IT_PROG |4800 | |IT |1400 |
|103 |60 |106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05 00:00:00.000|IT_PROG |4800 | |IT |1400 |
|103 |60 |107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07 00:00:00.000|IT_PROG |4200 | |IT |1400 |
|108 |100 |109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16 00:00:00.000|FI_ACCOUNT|9000 | |Finance |1700 |
|108 |100 |110 |John |Chen |JCHEN |515.124.4269|2005-09-28 00:00:00.000|FI_ACCOUNT|8200 | |Finance |1700 |
|108 |100 |111 |Ismael |Sciarra |ISCIARRA|515.124.4369|2005-09-30 00:00:00.000|FI_ACCOUNT|7700 | |Finance |1700 |
|108 |100 |112 |Jose Manuel|Urman |JMURMAN |515.124.4469|2006-03-07 00:00:00.000|FI_ACCOUNT|7800 | |Finance |1700 |
Snowflake¶
SELECT
*
FROM
hr.employees
NATURAL JOIN
hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|MANAGER_ID|DEPARTMENT_ID|EMPLOYEE_ID|FIRST_NAME |LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY |COMMISSION_PCT|DEPARTMENT_NAME|LOCATION_ID|
|----------|-------------|-----------|-----------|---------|--------|------------|----------|----------|--------|--------------|---------------|-----------|
|100 |90 |101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21|AD_VP |17000.00| |Executive |1700 |
|100 |90 |102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13|AD_VP |17000.00| |Executive |1700 |
|103 |60 |104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21|IT_PROG |6000.00 | |IT |1400 |
|103 |60 |105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25|IT_PROG |4800.00 | |IT |1400 |
|103 |60 |106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05|IT_PROG |4800.00 | |IT |1400 |
|103 |60 |107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07|IT_PROG |4200.00 | |IT |1400 |
|108 |100 |109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16|FI_ACCOUNT|9000.00 | |Finance |1700 |
|108 |100 |110 |John |Chen |JCHEN |515.124.4269|2005-09-28|FI_ACCOUNT|8200.00 | |Finance |1700 |
|108 |100 |111 |Ismael |Sciarra |ISCIARRA|515.124.4369|2005-09-30|FI_ACCOUNT|7700.00 | |Finance |1700 |
|108 |100 |112 |Jose Manuel|Urman |JMURMAN |515.124.4469|2006-03-07|FI_ACCOUNT|7800.00 | |Finance |1700 |
Cross Natural Join¶
Oracle¶
SELECT
*
FROM
hr.employees
CROSS NATURAL JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|MANAGER_ID|DEPARTMENT_ID|EMPLOYEE_ID|FIRST_NAME |LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY|COMMISSION_PCT|DEPARTMENT_NAME|LOCATION_ID|
|----------|-------------|-----------|-----------|---------|--------|------------|-----------------------|----------|------|--------------|---------------|-----------|
|100 |90 |101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21 00:00:00.000|AD_VP |17000 | |Executive |1700 |
|100 |90 |102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13 00:00:00.000|AD_VP |17000 | |Executive |1700 |
|103 |60 |104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21 00:00:00.000|IT_PROG |6000 | |IT |1400 |
|103 |60 |105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25 00:00:00.000|IT_PROG |4800 | |IT |1400 |
|103 |60 |106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05 00:00:00.000|IT_PROG |4800 | |IT |1400 |
|103 |60 |107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07 00:00:00.000|IT_PROG |4200 | |IT |1400 |
|108 |100 |109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16 00:00:00.000|FI_ACCOUNT|9000 | |Finance |1700 |
|108 |100 |110 |John |Chen |JCHEN |515.124.4269|2005-09-28 00:00:00.000|FI_ACCOUNT|8200 | |Finance |1700 |
|108 |100 |111 |Ismael |Sciarra |ISCIARRA|515.124.4369|2005-09-30 00:00:00.000|FI_ACCOUNT|7700 | |Finance |1700 |
|108 |100 |112 |Jose Manuel|Urman |JMURMAN |515.124.4469|2006-03-07 00:00:00.000|FI_ACCOUNT|7800 | |Finance |1700 |
Snowflake¶
SELECT
*
FROM
hr.employees
NATURAL JOIN
hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|MANAGER_ID|DEPARTMENT_ID|EMPLOYEE_ID|FIRST_NAME |LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY |COMMISSION_PCT|DEPARTMENT_NAME|LOCATION_ID|
|----------|-------------|-----------|-----------|---------|--------|------------|----------|----------|--------|--------------|---------------|-----------|
|100 |90 |101 |Neena |Kochhar |NKOCHHAR|515.123.4568|2005-09-21|AD_VP |17000.00| |Executive |1700 |
|100 |90 |102 |Lex |De Haan |LDEHAAN |515.123.4569|2001-01-13|AD_VP |17000.00| |Executive |1700 |
|103 |60 |104 |Bruce |Ernst |BERNST |590.423.4568|2007-05-21|IT_PROG |6000.00 | |IT |1400 |
|103 |60 |105 |David |Austin |DAUSTIN |590.423.4569|2005-06-25|IT_PROG |4800.00 | |IT |1400 |
|103 |60 |106 |Valli |Pataballa|VPATABAL|590.423.4560|2006-02-05|IT_PROG |4800.00 | |IT |1400 |
|103 |60 |107 |Diana |Lorentz |DLORENTZ|590.423.5567|2007-02-07|IT_PROG |4200.00 | |IT |1400 |
|108 |100 |109 |Daniel |Faviet |DFAVIET |515.124.4169|2002-08-16|FI_ACCOUNT|9000.00 | |Finance |1700 |
|108 |100 |110 |John |Chen |JCHEN |515.124.4269|2005-09-28|FI_ACCOUNT|8200.00 | |Finance |1700 |
|108 |100 |111 |Ismael |Sciarra |ISCIARRA|515.124.4369|2005-09-30|FI_ACCOUNT|7700.00 | |Finance |1700 |
|108 |100 |112 |Jose Manuel|Urman |JMURMAN |515.124.4469|2006-03-07|FI_ACCOUNT|7800.00 | |Finance |1700 |
Natural Cross Join¶
Oracle¶
SELECT
*
FROM
hr.employees
NATURAL CROSS JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL|PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME |MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|-----|------------|-----------------------|-------|------|--------------|----------|-------------|-------------|----------------|----------|-----------|
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |10 |Administration |200 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |100 |Finance |108 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |90 |Executive |100 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |80 |Sales |145 |2500 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |70 |Public Relations|204 |2700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |60 |IT |103 |1400 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |50 |Shipping |121 |1500 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |40 |Human Resources |203 |2400 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |30 |Purchasing |114 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17 00:00:00.000|AD_PRES|24000 | | |90 |20 |Marketing |201 |1800 |
Snowflake¶
SELECT
*
FROM
hr.employees
CROSS JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;
|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL|PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY |COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME |MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|-----|------------|----------|-------|--------|--------------|----------|-------------|-------------|----------------|----------|-----------|
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |80 |Sales |145 |2500 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |20 |Marketing |201 |1800 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |60 |IT |103 |1400 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |70 |Public Relations|204 |2700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |90 |Executive |100 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |30 |Purchasing |114 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |10 |Administration |200 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |100 |Finance |108 |1700 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |50 |Shipping |121 |1500 |
|100 |Steven |King |SKING|515.123.4567|2003-06-17|AD_PRES|24000.00| | |90 |40 |Human Resources |203 |2400 |
Problemas conhecidos¶
1. Results ordering mismatch between languages¶
O resultado da consulta terá o mesmo conteúdo em ambos os mecanismos de banco de dados, mas a ordem poderá ser diferente se nenhuma cláusula Order By for definida na consulta.
EWIs Relacionados ¶
Sem EWIs relacionados.
Junção externa¶
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Descrição¶
Uma junção externa amplia o resultado de uma junção simples. Uma junção externa retorna todas as linhas que satisfazem a condição de junção e retorna algumas ou todas as linhas de uma tabela para as quais nenhuma linha da outra tabela satisfaz a condição de junção. (Subseção Outer Joins da Referência da linguagem Oracle SQL).
Sintaxe do Oracle ANSI¶
[ query_partition_clause ] [ NATURAL ]
outer_join_type JOIN table_reference
[ query_partition_clause ]
[ ON condition
| USING ( column [, column ]...)
]
outer_join_type
{ FULL | LEFT | RIGHT } [ OUTER ]
O Oracle também oferece suporte ao operador (+), que pode ser usado para fazer junções externas. Esse operador é adicionado a uma expressão de coluna na cláusula WHERE.
column_expression (+)
Sintaxe do Snowflake ANSI¶
O Snowflake também suporta a sintaxe ANSI para OUTER JOINS, assim como o Oracle. No entanto, o comportamento ao usar o operador (+) pode ser diferente, dependendo do uso. Para obter mais informações sobre Snowflake Joins, veja aqui.
A gramática do Snowflake é uma das seguintes:
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¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Como o conjunto de resultados é muito grande, foi adicionada a cláusula Row Limiting. Você pode removê-lo para recuperar todo o conjunto de resultados.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Nota
Nos exemplos a seguir, as instruções inserts e alter foram executadas para distinguir melhor o resultado de cada tipo de JOIN:
INSERT INTO hr.regions VALUES (5, 'Oceania');
ALTER TABLE hr.countries DROP CONSTRAINT countr_reg_fk;
INSERT INTO hr.countries VALUES ('--', 'Unknown Country', 0);
1. ANSI syntax¶
O Snowflake é totalmente compatível com a sintaxe ANSI para SQL JOINS. O comportamento é o mesmo para ambos os mecanismos de banco de dados.
Left Outer Join On¶
Oracle¶
SELECT * FROM
hr.countries c
LEFT OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID|REGION_ID|REGION_NAME|
----------+---------------+---------+---------+-----------+
-- |Unknown Country| 0| | |
AR |Argentina | 2| 2|Americas |
AU |Australia | 3| 3|Asia |
BE |Belgium | 1| 1|Europe |
BR |Brazil | 2| 2|Americas |
CA |Canada | 2| 2|Americas |
CH |Switzerland | 1| 1|Europe |
CN |China | 3| 3|Asia |
DE |Germany | 1| 1|Europe |
DK |Denmark | 1| 1|Europe |
Snowflake¶
SELECT * FROM
hr.countries c
LEFT OUTER JOIN
hr.regions r ON c.region_id = r.region_id
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID |REGION_ID |REGION_NAME|
----------+---------------+---------------------+---------------------+-----------+
-- |Unknown Country|0.0000000000000000000| | |
AR |Argentina |2.0000000000000000000|2.0000000000000000000|Americas |
AU |Australia |3.0000000000000000000|3.0000000000000000000|Asia |
BE |Belgium |1.0000000000000000000|1.0000000000000000000|Europe |
BR |Brazil |2.0000000000000000000|2.0000000000000000000|Americas |
CA |Canada |2.0000000000000000000|2.0000000000000000000|Americas |
CH |Switzerland |1.0000000000000000000|1.0000000000000000000|Europe |
CN |China |3.0000000000000000000|3.0000000000000000000|Asia |
DE |Germany |1.0000000000000000000|1.0000000000000000000|Europe |
DK |Denmark |1.0000000000000000000|1.0000000000000000000|Europe |
Right Outer Join On¶
Oracle¶
SELECT * FROM
hr.countries c
RIGHT OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID|REGION_ID|REGION_NAME |
----------+------------------------+---------+---------+----------------------+
| | | 5|Oceania |
ZW |Zimbabwe | 4| 4|Middle East and Africa|
ZM |Zambia | 4| 4|Middle East and Africa|
US |United States of America| 2| 2|Americas |
UK |United Kingdom | 1| 1|Europe |
SG |Singapore | 3| 3|Asia |
NL |Netherlands | 1| 1|Europe |
NG |Nigeria | 4| 4|Middle East and Africa|
MX |Mexico | 2| 2|Americas |
ML |Malaysia | 3| 3|Asia |
Snowflake¶
SELECT * FROM
hr.countries c
RIGHT OUTER JOIN
hr.regions r ON c.region_id = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID |REGION_ID |REGION_NAME |
----------+------------------------+---------------------+---------------------+----------------------+
| | |5.0000000000000000000|Oceania |
ZW |Zimbabwe |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZM |Zambia |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
US |United States of America|2.0000000000000000000|2.0000000000000000000|Americas |
UK |United Kingdom |1.0000000000000000000|1.0000000000000000000|Europe |
SG |Singapore |3.0000000000000000000|3.0000000000000000000|Asia |
NL |Netherlands |1.0000000000000000000|1.0000000000000000000|Europe |
NG |Nigeria |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
MX |Mexico |2.0000000000000000000|2.0000000000000000000|Americas |
ML |Malaysia |3.0000000000000000000|3.0000000000000000000|Asia |
Full Outer Join On¶
Oracle¶
SELECT * FROM
hr.countries c
FULL OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY r.region_name DESC, c.country_id
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID|REGION_ID|REGION_NAME |
----------+---------------+---------+---------+----------------------+
-- |Unknown Country| 0| | |
| | | 5|Oceania |
EG |Egypt | 4| 4|Middle East and Africa|
IL |Israel | 4| 4|Middle East and Africa|
KW |Kuwait | 4| 4|Middle East and Africa|
NG |Nigeria | 4| 4|Middle East and Africa|
ZM |Zambia | 4| 4|Middle East and Africa|
ZW |Zimbabwe | 4| 4|Middle East and Africa|
BE |Belgium | 1| 1|Europe |
CH |Switzerland | 1| 1|Europe |
Snowflake¶
SELECT * FROM
hr.countries c
FULL OUTER JOIN
hr.regions r ON c.region_id = r.region_id
ORDER BY r.region_name DESC, c.country_id
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID |REGION_ID |REGION_NAME |
----------+---------------+---------------------+---------------------+----------------------+
-- |Unknown Country|0.0000000000000000000| | |
| | |5.0000000000000000000|Oceania |
EG |Egypt |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
IL |Israel |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
KW |Kuwait |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
NG |Nigeria |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZM |Zambia |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZW |Zimbabwe |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
BE |Belgium |1.0000000000000000000|1.0000000000000000000|Europe |
CH |Switzerland |1.0000000000000000000|1.0000000000000000000|Europe |
2. Natural Outer Join¶
Tanto o Oracle quanto o Snowflake suportam o Natural Outer Join e se comportam da mesma forma.
A NATURALJOIN é idêntica a uma JOIN explícita nas colunas comuns das duas tabelas, exceto que as colunas comuns são incluídas apenas uma vez na saída. (Uma junção natural pressupõe que colunas com o mesmo nome, mas em tabelas diferentes, contêm dados correspondentes.)(Referência da linguagem Snowflake SQL JOIN)
Natural Left Outer Join¶
Oracle¶
SELECT * FROM
hr.countries c
NATURAL LEFT OUTER JOIN hr.regions r
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID|COUNTRY_ID|COUNTRY_NAME |REGION_NAME|
---------+----------+---------------+-----------+
0|-- |Unknown Country| |
2|AR |Argentina |Americas |
3|AU |Australia |Asia |
1|BE |Belgium |Europe |
2|BR |Brazil |Americas |
2|CA |Canada |Americas |
1|CH |Switzerland |Europe |
3|CN |China |Asia |
1|DE |Germany |Europe |
1|DK |Denmark |Europe |
Snowflake¶
SELECT * FROM
hr.countries c
NATURAL LEFT OUTER JOIN
hr.regions r
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID |COUNTRY_ID|COUNTRY_NAME |REGION_NAME|
---------------------+----------+---------------+-----------+
0.0000000000000000000|-- |Unknown Country| |
2.0000000000000000000|AR |Argentina |Americas |
3.0000000000000000000|AU |Australia |Asia |
1.0000000000000000000|BE |Belgium |Europe |
2.0000000000000000000|BR |Brazil |Americas |
2.0000000000000000000|CA |Canada |Americas |
1.0000000000000000000|CH |Switzerland |Europe |
3.0000000000000000000|CN |China |Asia |
1.0000000000000000000|DE |Germany |Europe |
1.0000000000000000000|DK |Denmark |Europe |
Natural Right Outer Join¶
Oracle¶
SELECT * FROM
hr.countries c
NATURAL RIGHT OUTER JOIN hr.regions r
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
REGION_ID|COUNTRY_ID|COUNTRY_NAME |REGION_NAME |
---------+----------+------------------------+----------------------+
5| | |Oceania |
4|ZW |Zimbabwe |Middle East and Africa|
4|ZM |Zambia |Middle East and Africa|
2|US |United States of America|Americas |
1|UK |United Kingdom |Europe |
3|SG |Singapore |Asia |
1|NL |Netherlands |Europe |
4|NG |Nigeria |Middle East and Africa|
2|MX |Mexico |Americas |
3|ML |Malaysia |Asia |
Snowflake¶
SELECT * FROM
hr.countries c
NATURAL RIGHT OUTER JOIN
hr.regions r
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
REGION_ID |COUNTRY_ID|COUNTRY_NAME |REGION_NAME |
---------------------+----------+------------------------+----------------------+
5.0000000000000000000| | |Oceania |
4.0000000000000000000|ZW |Zimbabwe |Middle East and Africa|
4.0000000000000000000|ZM |Zambia |Middle East and Africa|
2.0000000000000000000|US |United States of America|Americas |
1.0000000000000000000|UK |United Kingdom |Europe |
3.0000000000000000000|SG |Singapore |Asia |
1.0000000000000000000|NL |Netherlands |Europe |
4.0000000000000000000|NG |Nigeria |Middle East and Africa|
2.0000000000000000000|MX |Mexico |Americas |
3.0000000000000000000|ML |Malaysia |Asia |
3. Basic Outer Join with USING¶
As colunas da tabela podem ser unidas usando a palavra-chave USING. Os resultados serão os mesmos de um OUTER JOIN básico com a palavra-chave ON.
Left Outer Join usando¶
_ Oracle _
SELECT * FROM
hr.countries c
LEFT OUTER JOIN hr.regions r USING (region_id)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID|COUNTRY_ID|COUNTRY_NAME |REGION_NAME|
---------+----------+---------------+-----------+
0|-- |Unknown Country| |
2|AR |Argentina |Americas |
3|AU |Australia |Asia |
1|BE |Belgium |Europe |
2|BR |Brazil |Americas |
2|CA |Canada |Americas |
1|CH |Switzerland |Europe |
3|CN |China |Asia |
1|DE |Germany |Europe |
1|DK |Denmark |Europe |
Snowflake¶
SELECT * FROM
hr.countries c
LEFT OUTER JOIN
hr.regions r USING (region_id)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID |COUNTRY_ID|COUNTRY_NAME |REGION_NAME|
---------------------+----------+---------------+-----------+
0.0000000000000000000|-- |Unknown Country| |
2.0000000000000000000|AR |Argentina |Americas |
3.0000000000000000000|AU |Australia |Asia |
1.0000000000000000000|BE |Belgium |Europe |
2.0000000000000000000|BR |Brazil |Americas |
2.0000000000000000000|CA |Canada |Americas |
1.0000000000000000000|CH |Switzerland |Europe |
3.0000000000000000000|CN |China |Asia |
1.0000000000000000000|DE |Germany |Europe |
1.0000000000000000000|DK |Denmark |Europe |
4. (+) Operator¶
O Oracle e o Snowflake têm um operador (+) que também pode ser usado para junções externas. Em alguns casos, o Snowflake pode não funcionar corretamente ao usar esse operador.
Para obter mais informações sobre esse operador no Snowflake, verifique isto.
Left Outer Join com o operador (+)¶
Oracle¶
SELECT * FROM hr.countries c, hr.regions r
WHERE c.region_id = r.region_id(+)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID|REGION_ID|REGION_NAME|
----------+---------------+---------+---------+-----------+
-- |Unknown Country| 0| | |
AR |Argentina | 2| 2|Americas |
AU |Australia | 3| 3|Asia |
BE |Belgium | 1| 1|Europe |
BR |Brazil | 2| 2|Americas |
CA |Canada | 2| 2|Americas |
CH |Switzerland | 1| 1|Europe |
CN |China | 3| 3|Asia |
DE |Germany | 1| 1|Europe |
DK |Denmark | 1| 1|Europe |
Snowflake¶
SELECT * FROM
hr.countries c,
hr.regions r
WHERE c.region_id = r.region_id(+)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID |REGION_ID |REGION_NAME|
----------+---------------+---------------------+---------------------+-----------+
-- |Unknown Country|0.0000000000000000000| | |
AR |Argentina |2.0000000000000000000|2.0000000000000000000|Americas |
AU |Australia |3.0000000000000000000|3.0000000000000000000|Asia |
BE |Belgium |1.0000000000000000000|1.0000000000000000000|Europe |
BR |Brazil |2.0000000000000000000|2.0000000000000000000|Americas |
CA |Canada |2.0000000000000000000|2.0000000000000000000|Americas |
CH |Switzerland |1.0000000000000000000|1.0000000000000000000|Europe |
CN |China |3.0000000000000000000|3.0000000000000000000|Asia |
DE |Germany |1.0000000000000000000|1.0000000000000000000|Europe |
DK |Denmark |1.0000000000000000000|1.0000000000000000000|Europe |
Right Outer Join com o operador (+)¶
Oracle¶
SELECT * FROM hr.countries c, hr.regions r
WHERE c.region_id (+) = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID|REGION_ID|REGION_NAME |
----------+------------------------+---------+---------+----------------------+
| | | 5|Oceania |
ZW |Zimbabwe | 4| 4|Middle East and Africa|
ZM |Zambia | 4| 4|Middle East and Africa|
US |United States of America| 2| 2|Americas |
UK |United Kingdom | 1| 1|Europe |
SG |Singapore | 3| 3|Asia |
NL |Netherlands | 1| 1|Europe |
NG |Nigeria | 4| 4|Middle East and Africa|
MX |Mexico | 2| 2|Americas |
ML |Malaysia | 3| 3|Asia |
Snowflake¶
SELECT * FROM
hr.countries c,
hr.regions r
WHERE c.region_id (+) = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID |REGION_ID |REGION_NAME |
----------+------------------------+---------------------+---------------------+----------------------+
| | |5.0000000000000000000|Oceania |
ZW |Zimbabwe |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZM |Zambia |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
US |United States of America|2.0000000000000000000|2.0000000000000000000|Americas |
UK |United Kingdom |1.0000000000000000000|1.0000000000000000000|Europe |
SG |Singapore |3.0000000000000000000|3.0000000000000000000|Asia |
NL |Netherlands |1.0000000000000000000|1.0000000000000000000|Europe |
NG |Nigeria |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
MX |Mexico |2.0000000000000000000|2.0000000000000000000|Americas |
ML |Malaysia |3.0000000000000000000|3.0000000000000000000|Asia |
Tabela única unida a várias tabelas com (+)¶
No Oracle, você pode unir uma única tabela a várias tabelas usando o operador (+); no entanto, o Snowflake não oferece suporte a isso. As consultas com esse tipo de junções externas serão alteradas para a sintaxe ANSI.
Oracle¶
SELECT
c.country_id,
c.country_name,
r.region_id,
r.region_name,
l.location_id,
l.street_address,
l.postal_code,
l.city
FROM
hr.countries c, hr.regions r, hr.locations l
WHERE
c.region_id(+) = r.region_id AND
l.country_id = c.country_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;
|COUNTRY_ID|COUNTRY_NAME |REGION_ID|REGION_NAME|LOCATION_ID|STREET_ADDRESS |POSTAL_CODE|CITY |
|----------|--------------|---------|-----------|-----------|----------------------------------------|-----------|-----------|
| | |1 |Europe |2000 |40-5-12 Laogianggen |190518 |Beijing |
|CH |Switzerland |1 |Europe |3000 |Murtenstrasse 921 |3095 |Bern |
| | |1 |Europe |2100 |1298 Vileparle (E) |490231 |Bombay |
|CH |Switzerland |1 |Europe |2900 |20 Rue des Corps-Saints |1730 |Geneva |
| | |1 |Europe |1300 |9450 Kamiya-cho |6823 |Hiroshima |
|UK |United Kingdom|1 |Europe |2400 |8204 Arthur St | |London |
| | |1 |Europe |3200 |Mariano Escobedo 9991 |11932 |Mexico City|
|DE |Germany |1 |Europe |2700 |Schwanthalerstr. 7031 |80925 |Munich |
|UK |United Kingdom|1 |Europe |2500 |Magdalen Centre, The Oxford Science Park|OX9 9ZB |Oxford |
|IT |Italy |1 |Europe |1000 |1297 Via Cola di Rie |00989 |Roma |
Snowflake¶
SELECT
c.country_id,
c.country_name,
r.region_id,
r.region_name,
l.location_id,
l.street_address,
l.postal_code,
l.city
FROM
hr.regions r
CROSS JOIN hr.locations l
LEFT OUTER JOIN
hr.countries c
ON
c.region_id = r.region_id
AND
l.country_id = c.country_id
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID |REGION_NAME|LOCATION_ID|STREET_ADDRESS |POSTAL_CODE|CITY |
----------+--------------+---------------------+-----------+-----------+----------------------------------------+-----------+-----------+
| |1.0000000000000000000|Europe | 2000|40-5-12 Laogianggen |190518 |Beijing |
CH |Switzerland |1.0000000000000000000|Europe | 3000|Murtenstrasse 921 |3095 |Bern |
| |1.0000000000000000000|Europe | 2100|1298 Vileparle (E) |490231 |Bombay |
CH |Switzerland |1.0000000000000000000|Europe | 2900|20 Rue des Corps-Saints |1730 |Geneva |
| |1.0000000000000000000|Europe | 1300|9450 Kamiya-cho |6823 |Hiroshima |
UK |United Kingdom|1.0000000000000000000|Europe | 2400|8204 Arthur St | |London |
| |1.0000000000000000000|Europe | 3200|Mariano Escobedo 9991 |11932 |Mexico City|
DE |Germany |1.0000000000000000000|Europe | 2700|Schwanthalerstr. 7031 |80925 |Munich |
UK |United Kingdom|1.0000000000000000000|Europe | 2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB |Oxford |
IT |Italy |1.0000000000000000000|Europe | 1000|1297 Via Cola di Rie |00989 |Roma |
Uso do operador (+) com uma coluna de uma tabela não unida e um valor que não é de uma coluna¶
No Oracle, você pode usar o operador (+) com uma coluna e uni-la a um valor que não seja uma coluna de outra tabela. O Snowflake também pode fazer isso, mas falhará se a tabela da coluna não tiver sido unida a outra tabela. Para resolver esse problema, o operador (+) é removido da consulta quando esse cenário ocorre e o resultado será o mesmo que no Oracle.
Oracle¶
SELECT * FROM hr.regions r
WHERE
r.region_name (+) LIKE 'A%'
ORDER BY region_id;
REGION_ID|REGION_NAME|
---------+-----------+
2|Americas |
3|Asia |
Snowflake¶
SELECT * FROM
hr.regions r
WHERE
r.region_name LIKE 'A%'
ORDER BY region_id;
REGION_ID |REGION_NAME|
---------------------+-----------+
2.0000000000000000000|Americas |
3.0000000000000000000|Asia |
Problemas conhecidos¶
Para todos os casos sem suporte, consulte os EWIs relacionados para obter recomendações e possíveis soluções alternativas.
1. Converted Outer Joins to ANSI syntax might reorder de columns¶
Quando uma consulta com uma junção externa não ANSI é convertida em uma junção externa ANSI, ela pode alterar a ordem das colunas na consulta convertida. Para corrigir esse problema, tente selecionar as colunas na ordem específica necessária.
Oracle¶
SELECT
*
FROM
hr.countries c, hr.regions r, hr.locations l
WHERE
c.region_id(+) = r.region_id AND
l.country_id = c.country_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME |REGION_ID|REGION_ID|REGION_NAME|LOCATION_ID|STREET_ADDRESS |POSTAL_CODE|CITY |STATE_PROVINCE |COUNTRY_ID|
----------+--------------+---------+---------+-----------+-----------+----------------------------------------+-----------+-----------+-----------------+----------+
| | | 1|Europe | 2000|40-5-12 Laogianggen |190518 |Beijing | |CN |
CH |Switzerland | 1| 1|Europe | 3000|Murtenstrasse 921 |3095 |Bern |BE |CH |
| | | 1|Europe | 2100|1298 Vileparle (E) |490231 |Bombay |Maharashtra |IN |
CH |Switzerland | 1| 1|Europe | 2900|20 Rue des Corps-Saints |1730 |Geneva |Geneve |CH |
| | | 1|Europe | 1300|9450 Kamiya-cho |6823 |Hiroshima | |JP |
UK |United Kingdom| 1| 1|Europe | 2400|8204 Arthur St | |London | |UK |
| | | 1|Europe | 3200|Mariano Escobedo 9991 |11932 |Mexico City|Distrito Federal,|MX |
DE |Germany | 1| 1|Europe | 2700|Schwanthalerstr. 7031 |80925 |Munich |Bavaria |DE |
UK |United Kingdom| 1| 1|Europe | 2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB |Oxford |Oxford |UK |
IT |Italy | 1| 1|Europe | 1000|1297 Via Cola di Rie |00989 |Roma | |IT |
Snowflake¶
SELECT
*
FROM
hr.regions r
CROSS JOIN hr.locations l
LEFT OUTER JOIN
hr.countries c
ON
c.region_id = r.region_id
AND
l.country_id = c.country_id
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;
REGION_ID |REGION_NAME|LOCATION_ID|STREET_ADDRESS |POSTAL_CODE|CITY |STATE_PROVINCE |COUNTRY_ID|COUNTRY_ID|COUNTRY_NAME |REGION_ID |
---------------------+-----------+-----------+----------------------------------------+-----------+-----------+-----------------+----------+----------+--------------+---------------------+
1.0000000000000000000|Europe | 2000|40-5-12 Laogianggen |190518 |Beijing | |CN | | | |
1.0000000000000000000|Europe | 3000|Murtenstrasse 921 |3095 |Bern |BE |CH |CH |Switzerland |1.0000000000000000000|
1.0000000000000000000|Europe | 2100|1298 Vileparle (E) |490231 |Bombay |Maharashtra |IN | | | |
1.0000000000000000000|Europe | 2900|20 Rue des Corps-Saints |1730 |Geneva |Geneve |CH |CH |Switzerland |1.0000000000000000000|
1.0000000000000000000|Europe | 1300|9450 Kamiya-cho |6823 |Hiroshima | |JP | | | |
1.0000000000000000000|Europe | 2400|8204 Arthur St | |London | |UK |UK |United Kingdom|1.0000000000000000000|
1.0000000000000000000|Europe | 3200|Mariano Escobedo 9991 |11932 |Mexico City|Distrito Federal,|MX | | | |
1.0000000000000000000|Europe | 2700|Schwanthalerstr. 7031 |80925 |Munich |Bavaria |DE |DE |Germany |1.0000000000000000000|
1.0000000000000000000|Europe | 2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB |Oxford |Oxford |UK |UK |United Kingdom|1.0000000000000000000|
1.0000000000000000000|Europe | 1000|1297 Via Cola di Rie |00989 |Roma | |IT |IT |Italy |1.0000000000000000000|
2. Outer joined between predicate with an interval with multiple tables¶
Predicados Between podem ser usado para OUTER JOINS não ANSI. No Oracle, as colunas dentro do intervalo podem ser unidas externamente, mesmo que venham de tabelas diferentes; no entanto, o Snowflake não oferece suporte a isso. Para esses casos, o predicado between será comentado.
Oracle¶
SELECT
*
FROM
hr.countries c, hr.regions r, hr.locations l WHERE
l.location_id BETWEEN r.region_id(+) AND c.region_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;
COUNTRY_ID|COUNTRY_NAME|REGION_ID|REGION_ID|REGION_NAME|LOCATION_ID|STREET_ADDRESS |POSTAL_CODE|CITY |STATE_PROVINCE |COUNTRY_ID|
----------+------------+---------+---------+-----------+-----------+----------------------------------------+-----------+-----------+-----------------+----------+
| | | 1|Europe | 2000|40-5-12 Laogianggen |190518 |Beijing | |CN |
| | | 1|Europe | 3000|Murtenstrasse 921 |3095 |Bern |BE |CH |
| | | 1|Europe | 2100|1298 Vileparle (E) |490231 |Bombay |Maharashtra |IN |
| | | 1|Europe | 2900|20 Rue des Corps-Saints |1730 |Geneva |Geneve |CH |
| | | 1|Europe | 1300|9450 Kamiya-cho |6823 |Hiroshima | |JP |
| | | 1|Europe | 2400|8204 Arthur St | |London | |UK |
| | | 1|Europe | 3200|Mariano Escobedo 9991 |11932 |Mexico City|Distrito Federal,|MX |
| | | 1|Europe | 2700|Schwanthalerstr. 7031 |80925 |Munich |Bavaria |DE |
| | | 1|Europe | 2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB |Oxford |Oxford |UK |
| | | 1|Europe | 1000|1297 Via Cola di Rie |00989 |Roma | |IT |
Snowflake¶
SELECT
*
FROM
hr.countries c,
hr.regions r,
hr.locations l WHERE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0090 - INVALID NON-ANSI OUTER JOIN BETWEEN PREDICATE CASE FOR SNOWFLAKE. ***/!!!
l.location_id BETWEEN r.region_id(+) AND c.region_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;
EWIs relacionados¶
SSC-EWI-OR0090: O Outer Join não Ansi tem um predicado Between inválido.
Self Join¶
Nota
Algumas partes dos códigos de saída foram omitidas por motivos de clareza.
Descrição¶
Uma self join é uma junção de uma tabela com ela mesma. Essa tabela aparece duas vezes na cláusula FROM e é seguida por aliases de tabela que qualificam os nomes de coluna na condição de junção. (Subseção Self Join da referência da linguagem Oracle SQL)
Amostra de padrões da origem¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Caso de Self Join básico¶
Oracle¶
SELECT e1.last_name||' works for '||e2.last_name
"Employees and Their Managers"
FROM hr.employees e1, hr.employees e2
WHERE e1.manager_id = e2.employee_id
AND e1.last_name LIKE 'R%'
ORDER BY e1.last_name;
Employees and Their Managers|
----------------------------+
Rajs works for Mourgos |
Raphaely works for King |
Rogers works for Kaufling |
Russell works for King |
Snowflake¶
SELECT
NVL( e1.last_name :: STRING, '') || ' works for ' || NVL(e2.last_name :: STRING, '') "Employees and Their Managers"
FROM
hr.employees e1,
hr.employees e2
WHERE e1.manager_id = e2.employee_id
AND e1.last_name LIKE 'R%'
ORDER BY e1.last_name;
Employees and Their Managers|
----------------------------+
Rajs works for Mourgos |
Raphaely works for King |
Rogers works for Kaufling |
Russell works for King |
Nota
Como provado anteriormente, self join no Oracle é funcionalmente equivalente ao Snowflake.
Problemas conhecidos¶
Não foram encontrados problemas.
EWIs relacionados¶
Sem EWIs relacionados.
Semijoin¶
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Descrição¶
Um semijoin retorna linhas que correspondem a uma subconsulta EXISTS sem duplicar as linhas do lado esquerdo do predicado quando várias linhas do lado direito satisfazem os critérios da subconsulta. A transformação semijoin não pode ser feita se a subconsulta estiver em um ramo OR da cláusula WHERE. (Subseção Semijoin da referência da linguagem Oracle SQL)
Amostra de padrões da origem¶
Nota
A cláusula Order by foi adicionada porque a ordem dos resultados pode variar entre o Oracle e o Snowflake.
Nota
Consulte esta seção para configurar o banco de dados de amostra.
Caso básico de Semijoin¶
Oracle¶
SELECT * FROM hr.departments
WHERE EXISTS
(SELECT * FROM hr.employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
DEPARTMENT_ID|DEPARTMENT_NAME |MANAGER_ID|LOCATION_ID|
-------------+----------------+----------+-----------+
110|Accounting | 205| 1700|
10|Administration | 200| 1700|
90|Executive | 100| 1700|
100|Finance | 108| 1700|
40|Human Resources | 203| 2400|
60|IT | 103| 1400|
20|Marketing | 201| 1800|
70|Public Relations| 204| 2700|
30|Purchasing | 114| 1700|
80|Sales | 145| 2500|
50|Shipping | 121| 1500|
Snowflake¶
SELECT * FROM
hr.departments
WHERE EXISTS
(SELECT * FROM
hr.employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
DEPARTMENT_ID|DEPARTMENT_NAME |MANAGER_ID|LOCATION_ID|
-------------+----------------+----------+-----------+
110|Accounting | 205| 1700|
10|Administration | 200| 1700|
90|Executive | 100| 1700|
100|Finance | 108| 1700|
40|Human Resources | 203| 2400|
60|IT | 103| 1400|
20|Marketing | 201| 1800|
70|Public Relations| 204| 2700|
30|Purchasing | 114| 1700|
80|Sales | 145| 2500|
50|Shipping | 121| 1500|
Nota
Como provado anteriormente, o semijoin no Oracle é funcionalmente equivalente ao Snowflake.
Problemas conhecidos¶
Não foram encontrados problemas.
EWIs relacionados¶
Sem EWIs relacionados.