Catégories :

Syntaxe de requête

WHERE

La clause WHERE spécifie une condition qui agit comme un filtre. Vous pouvez utiliser la clause WHERE pour :

  • Filtrer le résultat de la clause FROM dans une instruction SELECT.

  • Préciser les lignes à traiter dans une instruction UPDATE, MERGE, ou DELETE

Syntaxe

...
WHERE <predicate>
[ ... ]
Copy
predicate

Une expression booléenne. L’expression peut inclure des opérateurs logiques, tels que AND, OR et NOT.

Notes sur l’utilisation

  • Les prédicats de la clause WHERE se comportent comme s’ils étaient évalués après la clause FROM (bien que l’optimiseur puisse réorganiser les prédicats s’il n’y a pas d’incidence sur les résultats). Par exemple, si un prédicat de la clause WHERE fait référence aux colonnes d’une table participant à une jointure externe de la clause FROM, le filtre agit sur les lignes renvoyées par la jointure (pouvant être complétées avec des NULLs).

  • Faites attention lorsque vous créez des expressions qui pourraient correspondre à des valeurs NULLs.

    • Dans la plupart des contextes, l’expression booléenne NULL = NULL renvoie NULL et non TRUE. Pensez à utiliser IS [ NOT ] NULL pour comparer les valeurs NULL.

    • Dans une clause WHERE , si une expression correspond à NULL, la ligne correspondant à cette expression est supprimée du jeu de résultats (c’est-à-dire qu’elle est filtrée).

  • Le nombre maximum d’expressions dans une liste est 16 384. Par exemple, la limite s’applique au nombre d’expressions dans l’instruction SELECT suivante :

    SELECT column_x
   FROM mytable
   WHERE column_y IN (<expr1>, <expr2>, <expr3> ...);
    Copy

    Pour éviter d’atteindre la limite, effectuez une jointure avec une table de recherche contenant les valeurs de l’expression, plutôt que de spécifier les valeurs à l’aide de la clause IN. Par exemple, lorsque les valeurs de l’expression de l’exemple précédent sont ajoutées à une table de recherche nommée mylookuptable, vous pouvez exécuter la requête suivante avec succès, même si la table de recherche comporte plus de 16 384 lignes :

    SELECT column_x
  FROM mytable t
  JOIN mylookuptable l
  ON t.column_y = l.values_for_comparison;
    Copy

Jointures dans la clause WHERE

Bien que la clause WHERE soit principalement destinée au filtrage, la clause WHERE peut également être utilisée pour exprimer de nombreux types de jointures. Pour des informations conceptuelles sur les jointures, voir Utilisation des jointures (ou jonctions).

Une clause WHERE peut spécifier une jointure en incluant des conditions de jointure, qui sont des expressions booléennes définissant quelle(s) ligne(s) d’un côté de la jointure (JOIN) correspond(ent) à une(des) ligne(s) de l’autre côté de la jointure.

Les deux requêtes équivalentes suivantes montrent comment exprimer une jointure interne dans les clauses WHERE ou FROM :

SELECT t1.c1, t2.c2
    FROM t1, t2
    WHERE t1.c1 = t2.c2;

SELECT t1.c1, t2.c2
    FROM t1 INNER JOIN t2
        ON t1.c1 = t2.c2;
Copy

Les jointures externes peuvent être spécifiées en utilisant la syntaxe (+) dans la clause WHERE ou les mots-clés OUTER JOIN dans la clause FROM.

Lorsque vous spécifiez une jointure externe avec (+), la clause WHERE applique (+) à chaque colonne de jointure de la table qui est « interne » (définie ci-dessous).

Note

Le résultat d’une jointure externe contient une copie de toutes les lignes d’une table. Dans cette rubrique, la table dont les lignes sont conservées est appelée la table « externe » et l’autre table est appelée la table « interne ».

  • Dans une instruction LEFT OUTER JOIN, la table de gauche est la table externe et la table de droite est la table interne.

  • Dans une instruction RIGHT OUTER JOIN, la table de droite est la table externe et la table de gauche est la table interne.

Les requêtes suivantes présentent des jointures externes gauches équivalentes, dont l’une spécifie la jointure dans la clause FROM et l’autre dans la clause WHERE :

SELECT t1.c1, t2.c2
FROM t1 LEFT OUTER JOIN t2
        ON t1.c1 = t2.c2;

SELECT t1.c1, t2.c2
FROM t1, t2
WHERE t1.c1 = t2.c2(+);
Copy

Dans la deuxième requête, le (+) est à droite et identifie la table interne.

Voici un exemple de résultats pour les deux requêtes :

+-------+-------+
| T1.C1 | T2.C2 |
|-------+-------|
|     1 |     1 |
|     2 |  NULL |
|     3 |     3 |
|     4 |  NULL |
+-------+-------+
Copy

Si vous joignez une table sur plusieurs colonnes, utilisez la notation (+) sur chaque colonne de la table interne (t2 dans l’exemple ci-dessous) :

SELECT t1.c1, t2.c2
FROM t1, t2
WHERE t1.c1 = t2.c2 (+)
  AND t1.c3 = t2.c4 (+);
Copy

Note

Il existe de nombreuses restrictions quant à l’emplacement où l’annotation (+) peut apparaître. Les jointures externes de la clause FROM sont plus expressives. Snowflake suggère d’utiliser la notation (+) uniquement lors du transfert de code utilisant déjà cette notation. Le nouveau code devrait éviter cette notation.

Les restrictions comprennent :

  • Vous ne pouvez pas utiliser la notation (+) pour créer FULL OUTER JOIN. Vous ne pouvez créer que LEFT OUTER JOIN et RIGHT OUTER JOIN. Ceci n’est pas valide. L’instruction provoque le message d’erreur suivant : SQL compilation error: Outer join predicates form a cycle between 'T1' and 'T2'.

    -- NOT VALID
select t1.c1
    from t1, t2
    where t1.c1 (+) = t2.c2 (+);
    Copy

  • Si une table participe à plus d’une jointure dans une requête, la notation (+) peut spécifier la table comme table interne dans seulement une de ces jointures. Ce qui suit n’est pas valide, car t1 sert de table interne dans deux jointures. L’instruction provoque le message d’erreur suivant : SQL compilation error: Table 'T1' is outer joined to multiple tables: 'T3' and 'T2'.

    -- NOT VALID
select t1.c1
    from t1, t2, t3
    where t1.c1 (+) = t2.c2
      and t1.c1 (+) = t3.c3;
    Copy

    Notez cependant que vous pouvez utiliser (+) pour identifier différentes tables comme tables internes dans différentes jointures dans la même instruction SQL. L’exemple suivant joint trois tables : t1, t2, et t3, dont deux sont des tables internes (dans des jointures différentes). Cette instruction fonctionne :

    • Une instruction LEFT OUTER JOIN entre t1 et t2 (où t2 est la table intérieure).

    • Une instruction LEFT OUTER JOIN entre t2 et t3 (où t3 est la table intérieure).

    select t1.c1
    from t1, t2, t3
    where t1.c1 = t2.c2 (+)
      and t2.c2 = t3.c3 (+);
    Copy

Le (+) peut être immédiatement adjacent au nom de la table et de la colonne, ou être séparé par un espace. Les deux éléments suivants sont valides :

where t1.c1 = t2.c2(+)

where t1.c1 = t2.c2 (+)
Copy

Une requête peut contenir des jointures spécifiées à la fois dans la clause FROM ... ON ... et dans la clause WHERE. Cependant, le fait de spécifier des jointures dans différentes clauses d’une même requête peut rendre cette dernière plus difficile à lire.

La prise en charge des jointures dans la clause WHERE est principalement destinée à assurer la compatibilité ascendante avec les anciennes requêtes qui n’utilisent pas la syntaxe FROM ... ON .... Snowflake recommande d’utiliser FROM ... ON ... lors de l’écriture de nouvelles requêtes avec des jointures. Pour plus de détails, voir JOIN.

Exemples

Exemples simples de filtrage

Les exemples suivants montrent quelques utilisations simples de la clause WHERE :

SELECT * FROM invoices
  WHERE invoice_date < '2018-01-01';

SELECT * FROM invoices
  WHERE invoice_date < '2018-01-01'
    AND paid = FALSE;
Copy

Cet exemple utilise une sous-requête et affiche toutes les factures dont le montant de facturation est inférieur à la moyenne :

SELECT * FROM invoices
    WHERE amount < (
                   SELECT AVG(amount)
                       FROM invoices
                   )
    ;
Copy

Exécuter des jointures dans la clause WHERE

Pour spécifier une jointure dans la clause WHERE , énumérez les tables à joindre dans FROM clause, en séparant les tables par une virgule. Spécifiez la condition de jointure comme un filtre dans la clause WHERE , comme indiqué dans l’exemple suivant :

SELECT t1.col1, t2.col1
    FROM t1, t2
    WHERE t2.col1 = t1.col1
    ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+
Copy

Note

L’opérateur virgule est l’ancienne syntaxe de INNER JOIN. L’instruction suivante montre la façon recommandée de réaliser une jointure en utilisant une syntaxe plus récente. La requête ci-dessous est équivalente à la requête ci-dessus :

SELECT t1.col1, t2.col1
    FROM t1 JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+
Copy

La section suivante montre les jointures de table 3 et montre la différence de comportement avec les opérateurs de jointures extérieures 0, 1 ou 2 (+) .

Avant d’exécuter les requêtes, créez et chargez les tables à utiliser dans les jointures :

create table departments (
    department_ID INTEGER,
    department_name VARCHAR,
    location VARCHAR
    );
insert into departments (department_id, department_name, location) values
    (10, 'CUSTOMER SUPPORT', 'CHICAGO'),
    (40, 'RESEARCH', 'BOSTON'),
    (80, 'Department with no employees yet', 'CHICAGO'),
    (90, 'Department with no projects or employees yet', 'EREHWON')
    ;

create table projects (
    project_id integer,
    project_name varchar,
    department_id integer
    );
insert into projects (project_id, project_name, department_id) values
    (4000, 'Detect fake product reviews', 40),
    (4001, 'Detect false insurance claims', 10),
    (9000, 'Project with no employees yet', 80),
    (9099, 'Project with no department or employees yet', NULL)
    ;

create table employees (
    employee_ID INTEGER,
    employee_name VARCHAR,
    department_id INTEGER,
    project_id INTEGER
    );
insert into employees (employee_id, employee_name, department_id, project_id)
  values
    (1012, 'May Aidez', 10, NULL),
    (1040, 'Devi Nobel', 40, 4000),
    (1041, 'Alfred Mendeleev', 40, 4001)
    ;
Copy

Exécutez une jointure intérieure à 3 voies. Ceci n’utilise pas (+) (ou le mot-clé OUTER) et est donc une jointure intérieure. La sortie ne comprend que les lignes pour lesquelles il existe un département, un projet et un employé :

SELECT d.department_name, p.project_name, e.employee_name
    FROM  departments d, projects p, employees e
    WHERE
            p.department_id = d.department_id
        AND
            e.project_id = p.project_id
    ORDER BY d.department_id, p.project_id, e.employee_id;
+------------------+-------------------------------+------------------+
| DEPARTMENT_NAME  | PROJECT_NAME                  | EMPLOYEE_NAME    |
|------------------+-------------------------------+------------------|
| CUSTOMER SUPPORT | Detect false insurance claims | Alfred Mendeleev |
| RESEARCH         | Detect fake product reviews   | Devi Nobel       |
+------------------+-------------------------------+------------------+
Copy

Effectuer une jointure extérieure. Cela est similaire à l’instruction précédente sauf que celle-ci utilise (+) pour faire de la deuxième jointure une jointure extérieure droite. L’effet est que si un département est inclus dans la sortie, alors tous les projets de ce département sont inclus, même si ces projets n’ont pas d’employés :

SELECT d.department_name, p.project_name, e.employee_name
    FROM  departments d, projects p, employees e
    WHERE
            p.department_id = d.department_id
        AND
            e.project_id(+) = p.project_id
    ORDER BY d.department_id, p.project_id, e.employee_id;
+----------------------------------+-------------------------------+------------------+
| DEPARTMENT_NAME                  | PROJECT_NAME                  | EMPLOYEE_NAME    |
|----------------------------------+-------------------------------+------------------|
| CUSTOMER SUPPORT                 | Detect false insurance claims | Alfred Mendeleev |
| RESEARCH                         | Detect fake product reviews   | Devi Nobel       |
| Department with no employees yet | Project with no employees yet | NULL             |
+----------------------------------+-------------------------------+------------------+
Copy

Effectuer deux jointures extérieures. Cela est similaire à l’instruction précédente sauf que celle-ci utilise (+) pour faire des deux jointures des jointures extérieures. L’effet est que tous les départements sont inclus (même s’ils n’ont pas encore de projets ou d’employés) et tous les projets associés aux départements sont inclus (même s’ils n’ont pas encore d’employés). Notez que la sortie exclut les projets qui n’ont pas de département.

SELECT d.department_name, p.project_name, e.employee_name
    FROM  departments d, projects p, employees e
    WHERE
            p.department_id(+) = d.department_id
        AND
            e.project_id(+) = p.project_id
    ORDER BY d.department_id, p.project_id, e.employee_id;
+----------------------------------------------+-------------------------------+------------------+
| DEPARTMENT_NAME                              | PROJECT_NAME                  | EMPLOYEE_NAME    |
|----------------------------------------------+-------------------------------+------------------|
| CUSTOMER SUPPORT                             | Detect false insurance claims | Alfred Mendeleev |
| RESEARCH                                     | Detect fake product reviews   | Devi Nobel       |
| Department with no employees yet             | Project with no employees yet | NULL             |
| Department with no projects or employees yet | NULL                          | NULL             |
+----------------------------------------------+-------------------------------+------------------+
Copy

(N’oubliez pas, cependant, que Snowflake recommande d’utiliser le mot-clé OUTER dans la clause FROM plutôt que d’utiliser l’opérateur (+) dans la clause WHERE).

Langage: Français