SnowConvert : Jointures Oracle¶
Une jointure est une requête qui combine les lignes de deux ou plusieurs tables, vues ou vues matérialisées. La base de données Oracle effectue une jointure lorsque plusieurs tables apparaissent dans la clause FROM de la requête.(Référence linguistique Oracle SQL JOINS)
Voici les articles de cette section :
Antijointure¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Une antijointure renvoie les lignes du côté gauche du prédicat pour lesquelles il n’y a pas de lignes correspondantes du côté droit du prédicat. Il renvoie les lignes qui ne correspondent pas (NOT IN) à la sous-requête du côté droit. La transformation antijointure ne peut pas être effectuée si la sous-requête se trouve sur une branche OR de la clause WHERE. (Référence linguistique Oracle SQL Antijointure).
Aucune transformation spéciale n’est effectuée pour ce type de jointure puisque Snowflake prend en charge la même syntaxe.
Modèles d’échantillons de sources¶
Note
Ajout de la clause Order by car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Le jeu de résultats étant trop large, la clause de limitation des lignes a été ajoutée. Vous pouvez la supprimer pour récupérer le jeu de résultats.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Where Not In¶
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 Exists¶
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 |
Problèmes connus¶
1. Results ordering mismatch between languages¶
Le résultat de la requête aura le même contenu dans les deux moteurs de base de données, mais l’ordre peut être différent si aucune clause Order By n’est définie dans la requête.
Jointure par bande¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Une jointure par bande est un type particulier de jointure sans équivoque dans lequel les valeurs clés d’un ensemble de données doivent se situer dans la plage spécifiée (« bande ») du second ensemble de données. La même table peut servir à la fois de premier et de second ensembles de données. (Référence linguistique Oracle SQL BandJoin)
Dans cette section, nous verrons comment une jointure par bande est exécutée dans Snowflake et le plan d’exécution est très similaire à la version améliorée d’Oracle.
Modèles d’échantillons de sources¶
Note
Ajout de la clause Order by car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Le jeu de résultats étant trop large, la clause de limitation des lignes a été ajoutée. Vous pouvez la supprimer pour récupérer le jeu de résultats.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Avertissement
Si vous migrez ce code sans créer de tables, le convertisseur ne sera pas en mesure de charger les informations sémantiques des colonnes et un avertissement apparaîtra lors des opérations arithmétiques.
Cas simple de Jointure par bande¶
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 |
Avertissement
La migration de certaines instructions SELECT sans les tables correspondantes pourrait générer le problème suivant : SSC-EWI-OR0036 : Problèmes de résolution de types. Pour éviter cet avertissement, incluez CREATE TABLE dans le fichier.
Les résultats sont les mêmes, ce qui rend la fonctionnalité BAND JOIN équivalente.
Plan d’exécution
À titre d’information supplémentaire, la particularité des jointures par bande est le plan d’exécution.
L’image suivante montre le plan d’exécution amélioré (mis en œuvre depuis Oracle 12c) pour la requête de test :
Dans l’image suivante, nous voyons le plan d’exécution dans Snowflake :
Note
Le plan d’exécution dans Snowflake est très similaire à la version optimisée d’Oracle. La durée et les performances finales de la requête seront affectées par de nombreux autres facteurs et dépendent entièrement de chaque fonctionnalité interne DBMS.
Problèmes connus¶
1. Results ordering mismatch between languages¶
Le résultat de la requête aura le même contenu dans les deux moteurs de base de données, mais l’ordre peut être différent si aucune clause Order By n’est définie dans la requête.
EWIs connexes¶
SSC-EWI-OR0036: Problèmes de résolution de types, l’opération arithmétique peut ne pas se comporter correctement entre une chaîne et une date.
Produits cartésiens¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Si deux tables dans une requête de jointure n’ont pas de condition de jointure, Oracle Database renvoie leur produit cartésien. Oracle combine chaque ligne d’une table avec chaque ligne de l’autre table. (Référence Oracle SQL Sous-section Produits cartésiens)
Oracle et Snowflake sont également compatibles avec la syntaxe de jointure croisée ANSI qui a le même comportement qu’un produit cartésien.
Aucune transformation spéciale n’est effectuée pour ce type de jointure puisque Snowflake prend en charge la même syntaxe.
Modèles d’échantillons de sources¶
Note
La clause Order by a été ajoutée car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Le jeu de résultats étant trop large, la clause de limitation des lignes a été ajoutée. Vous pouvez la supprimer pour récupérer le jeu de résultats.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Syntaxe implicite¶
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 |
Syntaxe de jointure croisée¶
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;
Problèmes connus¶
1. Results ordering mismatch between languages¶
Le résultat de la requête aura le même contenu dans les deux moteurs de base de données, mais l’ordre peut être différent si aucune clause Order By n’est définie dans la requête.
EWIs connexes¶
Pas d’EWIs connexes.
Jointure par égalité¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Une jointure par égalité est une forme implicite de jointure avec une condition de jointure contenant un opérateur d’égalité. Pour plus d’informations sur les jointures par égalité Oracle, cliquez ici.
Aucune transformation spéciale n’est effectuée pour ce type de jointure puisque Snowflake prend en charge la même syntaxe.
Modèles d’échantillons de sources¶
Note
Ajout de la clause Order by car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Comme le jeu de résultats est trop important, la clause de limitation des lignes a été ajoutée. Vous pouvez la supprimer pour récupérer le jeu de résultats.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Cas simple de jointure par égalité¶
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|
Problèmes connus¶
1. Results ordering mismatch between languages¶
Le résultat de la requête aura le même contenu dans les deux moteurs de base de données, mais l’ordre peut être différent si aucune clause Order By n’est définie dans la requête.
EWIs connexes¶
Pas d’EWIs connexes.
Jointure intérieure¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Une jointure interne (parfois appelée jointure simple) est une jointure de deux tables ou plus qui renvoie uniquement les lignes qui satisfont à la condition de jointure. (Référence Oracle SQL Sous-section Jointure interne).
{ [ INNER ] JOIN table_reference
{ ON condition
| USING (column [, column ]...)
}
| { CROSS
| NATURAL [ INNER ]
}
JOIN table_reference
}
Modèles d’échantillons de sources¶
Note
Ajout de la clause Order by car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Le jeu de résultats étant trop large, la clause de limitation des lignes a été ajoutée. Vous pouvez supprimer cette clause pour récupérer le jeu de résultats.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Jointure interne simple¶
Dans la clause de jointure interne, « INNER » est un mot-clé facultatif, les requêtes suivantes ont deux selects qui récupèrent le même jeu de données.
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 |
Jointure interne avec clause d’utilisation¶
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 |
Jointure interne croisée¶
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 |
Joint intérieur naturel¶
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 |
Jointure naturelle croisée¶
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 |
Jointure croisée naturelle¶
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 |
Problèmes connus¶
1. Results ordering mismatch between languages¶
Le résultat de la requête aura le même contenu dans les deux moteurs de base de données, mais l’ordre peut être différent si aucune clause Order By n’est définie dans la requête.
EWIs connexes ¶
Pas d’EWIs connexes.
Jointure extérieure¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Une jointure externe étend le résultat d’une jointure simple. Une jointure externe renvoie toutes les lignes qui satisfont à la condition de jointure et renvoie certaines ou toutes les lignes d’une table pour lesquelles aucune ligne de l’autre table ne satisfait à la condition de jointure. (Référence linguistique Oracle SQL Sous-section Jointures externes).
Syntaxe 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 ]
Oracle prend également en charge l’opérateur (+) qui peut être utilisé pour effectuer des jointures externes. Cet opérateur est ajouté à une expression de colonne dans la clause WHERE.
column_expression (+)
Syntaxe Snowflake ANSI¶
Snowflake prend également en charge la syntaxe ANSI pour OUTER JOINS, tout comme Oracle. Toutefois, le comportement lors de l’utilisation de l’opérateur (+) peut être différent en fonction de l’utilisation. Pour plus d’informations sur les jointures Snowflake, cliquez ici.
La grammaire Snowflake est l’une des suivantes :
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>
[ ... ]
Modèles d’échantillons de sources¶
Note
Ajout de la clause Order by car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Le jeu de résultats étant trop large, la clause de limitation des lignes a été ajoutée. Vous pouvez la supprimer pour récupérer le jeu de résultats.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Note
Pour les exemples suivants, ces instructions d’insertion et de modification ont été exécutées afin de mieux distinguer le résultat pour chaque type 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¶
Snowflake prend entièrement en charge la syntaxe ANSI pour SQL JOINS. Le comportement est le même pour les deux moteurs de base de données.
Jointure externe gauche sur¶
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 |
Jointure externe droite sur¶
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 |
Jointure externe complète sur¶
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¶
Oracle et Snowflake prennent tous deux en charge la jointure externe naturelle et se comportent de la même manière.
Un NATURALJOIN est identique à un JOIN explicite sur les colonnes communes des deux tables, à ceci près que les colonnes communes ne sont incluses qu’une seule fois dans la sortie. (Une jointure naturelle suppose que les colonnes portant le même nom, mais situées dans des tables différentes, contiennent des données correspondantes.) (Référence linguistique Snowflake SQL JOIN)
Jointure externe gauche naturelle¶
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 |
Jointure externe droite naturelle¶
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¶
Les colonnes d’une table peuvent être jointes à l’aide du mot-clé USING. Les résultats seront les mêmes que ceux d’une recherche de base sur OUTER JOIN avec le mot-clé ON.
Jointure externe gauche Using¶
_ 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¶
Oracle et Snowflake disposent d’un opérateur (+) qui peut également être utilisé pour les jointures externes. Dans certains cas, Snowflake peut ne pas fonctionner correctement lorsque vous utilisez cet opérateur.
Pour plus d’informations concernant cet opérateur dans Snowflake, cliquez ici.
Jointure externe gauche avec l’opérateur (+)¶
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 |
Jointure externe droite avec l’opérateur (+)¶
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 |
Table unique jointe à plusieurs tables avec (+)¶
Dans Oracle, vous pouvez joindre une table unique à plusieurs tables à l’aide de l’opérateur (+), mais Snowflake ne prend pas en charge cette opération. Les requêtes contenant ce type de jointures externes seront converties en syntaxe 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 |
Utilisation de l’opérateur (+) avec une colonne d’une table non jointe et une valeur autre qu’une colonne¶
Dans Oracle, vous pouvez utiliser l’opérateur (+) avec une colonne et la joindre à une valeur qui n’est pas une colonne d’une autre table. Snowflake peut également le faire, mais il échouera si la table de la colonne n’a pas été jointe à une autre table. Pour résoudre ce problème, l’opérateur (+) est supprimé de la requête lorsque ce scénario se produit et le résultat sera le même que dans 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 |
Problèmes connus¶
Pour tous les cas non pris en charge, veuillez consulter les EWIs pour obtenir des recommandations et des solutions de contournement.
1. Converted Outer Joins to ANSI syntax might reorder de columns¶
Lorsqu’une requête comportant une jointure externe non-ANSI est convertie en jointure externe ANSI, l’ordre des colonnes peut être modifié dans la requête convertie. Pour corriger ce problème, essayez de sélectionner les colonnes dans l’ordre spécifique requis.
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¶
Les prédicats entre peuvent être utilisés pour les OUTER JOINS non-ANSI. Dans Oracle, les colonnes à l’intérieur de l’intervalle peuvent faire l’objet d’une jointure externe, même si elles proviennent de tables différentes. Cependant, Snowflake ne prend pas en charge ce cas. Dans ces cas, le prédicat entre sera commenté.
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 connexes¶
SSC-EWI-OR0090 : La jointure externe non-Ansi a un prédicat Between invalide.
Autojointure¶
Note
Certaines parties des codes de sortie sont omises pour des raisons de clarté.
Description¶
Une autojointure est une jointure d’une table à elle-même. Cette table apparaît deux fois dans la clause FROM et est suivie d’alias de table qui qualifient les noms de colonne dans la condition de jointure. (Référence linguistique Oracle SQL Sous-section Autojointure)
Modèles d’échantillons de sources¶
Note
Ajout de la clause Order by car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Cas simple d’Autojointure¶
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 |
Note
Comme nous l’avons prouvé précédemment, la fonctionnalité autojointure dans Oracle est équivalente à celle de Snowflake.
Problèmes connus¶
Aucun problème n’a été constaté.
EWIs connexes¶
Pas d’EWIs connexes.
Semi-jointure¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Une semi-jointure renvoie les lignes qui correspondent à une sous-requête EXISTS sans dupliquer les lignes du côté gauche du prédicat lorsque plusieurs lignes du côté droit satisfont aux critères de la sous-requête. La transformation en sous-requête ne peut pas être effectuée si la sous-requête se trouve sur une branche OR de la clause WHERE. (Référence linguistique Oracle SQL Sous-section Semi-jointure)
Modèles d’échantillons de sources¶
Note
Ajout de la clause Order by car l’ordre des résultats peut varier entre Oracle et Snowflake.
Note
Consultez cette section pour établir l’ensemble de la base de données de l’échantillon.
Cas simple de semi-jointure¶
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|
Note
Comme nous l’avons prouvé précédemment, la semi-jointure Oracle est fonctionnellement équivalente à Snowflake.
Problèmes connus¶
Aucun problème n’a été constaté.
EWIs connexes¶
Pas d’EWIs connexes.