Portage de la sémantique des NULL et des chaînes vides entre Oracle, SQL Server, MySQL et SQLite
Portez la gestion des NULL entre Oracle, SQL Server, MySQL et SQLite en fixant quatre points de portabilité : le test des NULL, le résultat des comparaisons, la coercition booléenne et la signification des chaînes vides. Oracle traite les chaînes vides comme NULL, tandis que SQL Server dépend de ANSI_NULLS et SQLite utilise des extensions spécifiques.
Dans ce guide
Idée principale
Pour porter la gestion des NULL entre Oracle, SQL Server, MySQL et SQLite, il faut résoudre quatre points de portabilité : la méthode de test d'un NULL, le résultat d'une comparaison avec NULL, la coercition des NULL dans les contextes booléens et la définition d'une chaîne vide. L'élément le plus risqué est Oracle, qui traite actuellement une valeur de caractère de longueur zéro comme NULL, bien que sa documentation avertisse que cela pourrait changer et recommande de ne pas assimiler les chaînes vides aux NULL. Utilisez IS NULL et IS NOT NULL pour les tests, car toute autre condition impliquant NULL évalue à UNKNOWN ; UNKNOWN se comporte presque comme FALSE dans une clause WHERE, mais NOT UNKNOWN reste UNKNOWN. SQL Server introduit un compromis de configuration : avec SET ANSI_NULLS ON, un ou deux opérandes NULL produisent UNKNOWN, tandis qu'avec ANSI_NULLS OFF, les opérateurs d'égalité et d'inégalité traitent NULL comme une valeur connue égale aux autres NULL et retournent uniquement TRUE ou FALSE, modifiant ainsi les résultats du filtrage selon les paramètres de session. MySQL maintient que les comparaisons avec NULL retournent NULL, mais son contexte booléen traite 0 ou NULL comme faux et tout autre élément comme vrai ; il considère deux NULL comme égaux dans un GROUP BY et place les NULL en premier pour ASC et en dernier pour DESC. SQLite propose des opérateurs compacts IS et IS NOT qui retournent toujours 1 ou 0 et jamais NULL, mais précise qu'il s'agit d'une extension, le SQL standard exigeant IS NOT DISTINCT FROM et IS DISTINCT FROM. Le compromis réside entre la cohérence et la portabilité : les formes spécifiques aux moteurs sont concises localement, tandis que l'utilisation de IS NULL / IS NOT NULL combinée à une gestion explicite de type COALESCE est la seule syntaxe prévisible partout. Documentez ces quatre points par moteur dans une matrice écrite plutôt que de supposer que le comportement SQL standard s'applique, et ajoutez PostgreSQL comme ligne à vérifier via sa propre documentation.
Contexte et recommandation
Avant de porter des requêtes ou des modules SQL partagés entre Oracle, SQL Server, MySQL et SQLite, établissez une liste de contrôle de portabilité explicite pour les NULL : (1) comment un NULL est testé, (2) ce qu'une comparaison avec NULL retourne, (3) si les contextes booléens coercent le NULL, et (4) ce que signifie une chaîne vide. Le risque le plus élevé concerne Oracle, qui traite actuellement une valeur de caractère de longueur zéro comme NULL, tout en recommandant explicitement de ne pas s'appuyer sur cette similitude. Consignez ces quatre points par moteur dans une matrice plutôt que de présumer que le comportement SQL standard est universel, et vérifiez PostgreSQL via sa propre documentation.
La règle fondamentale de portabilité est de tester les NULL uniquement avec IS NULL et IS NOT NULL. Oracle précise que ce sont les seules comparaisons à utiliser ; toute autre condition impliquant NULL évalue à UNKNOWN. UNKNOWN se comporte presque comme FALSE dans une clause WHERE, mais NOT UNKNOWN reste UNKNOWN au lieu de devenir TRUE. SQL Server présente un arbitrage de configuration : avec SET ANSI_NULLS ON, un ou deux opérandes NULL produisent UNKNOWN, alors qu'avec ANSI_NULLS OFF, les opérateurs = et <> traitent NULL comme une valeur connue égale aux autres NULL, retournant uniquement TRUE ou FALSE, ce qui modifie les résultats selon la session. MySQL retourne NULL pour les comparaisons avec NULL, mais son contexte booléen traite 0 ou NULL comme faux. Il considère deux NULL comme égaux dans GROUP BY et place les NULL en premier pour ASC et en dernier pour DESC. SQLite propose des opérateurs IS et IS NOT compacts retournant toujours 1 ou 0, mais stipule qu'il s'agit d'une extension, le SQL standard requérant IS NOT DISTINCT FROM et IS DISTINCT FROM.
Le compromis oppose la concision locale à la portabilité globale : les formes spécifiques aux moteurs sont lisibles localement, mais IS NULL / IS NOT NULL avec COALESCE est la seule approche prévisible partout. Ne supposez pas qu'une requête écrite pour un moteur fonctionnera sans modification sur un autre, ni qu'une chaîne vide et un NULL sont interchangeables. La matrice doit capturer les quatre points par moteur ainsi que les paramètres de session influençant les résultats, servant de guide pour la revue de code.
Utilisez les extraits sources comme preuves : la documentation Oracle impose IS NULL et IS NOT NULL, car toute autre condition évalue à UNKNOWN, et précise que Oracle traite actuellement la longueur zéro comme NULL tout en déconseillant cette pratique. SQL Server indique que SET ANSI_NULLS ON produit UNKNOWN pour les expressions NULL, tandis que SET ANSI_NULLS OFF traite NULL comme une valeur connue. MySQL montre que 1 = NULL, 1 <> NULL, 1 < NULL et 1 > NULL retournent tous NULL, et que '' IS NULL est 0 alors que '' IS NOT NULL est 1. SQLite indique qu'une expression IS ou IS NOT ne peut pas évaluer à NULL, que les formes compactes sont une extension et que le standard SQL exige IS NOT DISTINCT FROM.
-- portable across the engines covered here
SELECT
'' IS NULL AS empty_is_null,
'' IS NOT NULL AS empty_is_not_null,
1 = NULL AS eq_null,
1 <> NULL AS ne_null;
-- run the same probe twice on SQL Server: once with ANSI_NULLS ON, once OFF
SET ANSI_NULLS ON;
SELECT 1 = NULL;
SET ANSI_NULLS OFF;
SELECT 1 = NULL;
-- row filtering: only this form is portable
SELECT * FROM t WHERE col IS NOT NULL;Raisonnement et compromis
Les moteurs s'accordent sur la règle de base mais divergent sur les cas limites qui brisent la portabilité. Oracle stipule que IS NULL et IS NOT NULL sont les seules comparaisons valides pour les NULL, toute autre condition évaluant à UNKNOWN, lequel reste UNKNOWN même après une négation. SQL Server propose un choix de configuration : ANSI_NULLS ON produit UNKNOWN, tandis qu'ANSI_NULLS OFF traite NULL comme une valeur connue, changeant ainsi les résultats du filtrage selon la session. MySQL retourne NULL pour les comparaisons, mais coerce 0 et NULL en faux dans les contextes booléens, et gère les NULL comme égaux dans GROUP BY avec un tri spécifique (premier en ASC, dernier en DESC). SQLite offre des opérateurs IS et IS NOT qui ne retournent jamais NULL, mais précise qu'il s'agit d'une extension non standard.
Le choix se porte entre la concision et la portabilité. Les formes spécifiques, comme celles de SQLite, sont concises mais ne fonctionneront pas sur des moteurs exigeant IS NOT DISTINCT FROM. Le comportement d'Oracle concernant les chaînes vides est concis mais sensible à la version. Le paramètre ANSI_NULLS de SQL Server peut modifier silencieusement le sens de = et <>, rendant une requête apparemment portable dépendante de la configuration de session. La coercition booléenne de MySQL s'applique aux contextes booléens et non aux comparaisons = et <>, ce qui signifie qu'une requête fonctionnant dans un WHERE pourrait échouer dans une expression CASE.
La matrice doit donc enregistrer non seulement les résultats des tests, mais aussi les paramètres de session. Pour SQL Server, vérifiez le paramètre ANSI_NULLS réel. Pour Oracle, notez la version et retestez après les mises à jour, car la règle de la chaîne vide pourrait changer. Pour MySQL, distinguez les contextes booléens des opérateurs de comparaison et notez le comportement de ORDER BY. Pour SQLite, rappelez-vous que IS et IS NOT sont des extensions. La matrice doit également indiquer si le moteur supporte IS NOT DISTINCT FROM, qui est la syntaxe portable pour l'égalité des NULL en SQL standard.
La recommandation est d'utiliser IS NULL et IS NOT NULL pour les tests, et COALESCE pour les valeurs potentiellement NULL. C'est la seule syntaxe prévisible car elle ne dépend ni du traitement des chaînes vides, ni des paramètres de session, ni du contexte booléen. Pour distinguer une chaîne vide d'un NULL, utilisez des colonnes séparées ou des valeurs sentinelles. La matrice doit servir de liste de contrôle lors des revues de code et être mise à jour à chaque nouvelle version de moteur.
Illustration concrète
Exécutez la même sonde sur chaque moteur et enregistrez les résultats : SELECT '' IS NULL, '' IS NOT NULL, 1 = NULL, 1 <> NULL, 0 = NULL, ainsi qu'un test de contexte booléen comme SELECT NOT NULL. Sur Oracle, la chaîne vide se comporte comme NULL (bien que la documentation avertisse d'un possible changement) ; MySQL documente '' IS NULL comme 0 et '' IS NOT NULL comme 1, et 1 = NULL comme NULL ; SQLite retourne 1 ou 0 pour IS et IS NOT. Enchaînez avec un test de filtrage, WHERE value <> NULL et WHERE value IS NOT NULL, pour voir les lignes retournées. Utilisez une table de référence pour rendre explicites les différences entre chaînes vides et NULL.
La table de référence doit contenir un NULL, une chaîne vide, un zéro et une valeur normale. Sur Oracle, la chaîne vide est stockée comme NULL, donc '' IS NULL sera 1 et '' IS NOT NULL sera 0, tandis que 1 = NULL retournera NULL. Sur MySQL, '' IS NULL sera 0 et '' IS NOT NULL sera 1, et toutes les comparaisons avec NULL ( = , <> , < , > ) retourneront NULL. Sur SQLite, '' IS NULL sera 0, '' IS NOT NULL sera 1, et IS/IS NOT retourneront toujours 1 ou 0. Sur SQL Server, exécutez le test deux fois (ANSI_NULLS ON et OFF), car les opérateurs = et <> retournent UNKNOWN dans le premier cas et TRUE/FALSE dans le second.
Le test de filtrage montrera que WHERE value <> NULL ne retourne aucune ligne sur Oracle, MySQL et SQLite, car la comparaison retourne NULL et UNKNOWN agit comme FALSE dans un WHERE. WHERE value IS NOT NULL est la seule forme portable retournant toutes les lignes non NULL. Sur SQL Server, WHERE value <> NULL peut retourner des lignes si ANSI_NULLS est OFF. Le test de contexte booléen montrera que NOT NULL évalue à UNKNOWN dans les moteurs suivant la logique trivalente, et qu'un WHERE avec UNKNOWN ne retourne aucune ligne, alors qu'un contexte coercant UNKNOWN en FALSE pourrait différer.
Consignez les résultats littéraux dans la matrice, incluant le paramètre de session pour SQL Server. Notez le support de IS NOT DISTINCT FROM et la stabilité de la règle de la chaîne vide pour Oracle. Ces résultats servent de preuves pour choisir la syntaxe dans les modules SQL partagés. La syntaxe portable reste IS NULL et IS NOT NULL avec COALESCE, car elle est indépendante du traitement des chaînes vides, des paramètres de session et du contexte booléen.
Limites d'applicabilité
Ce conseil couvre uniquement la comparaison des NULL et la sémantique des chaînes vides ; il ne traite pas le comptage d'agrégats, le comportement des index ou les performances. Les sources n'incluent pas la documentation PostgreSQL, donc aucun comportement n'est affirmé pour ce moteur et doit être vérifié séparément. La règle d'Oracle sur les caractères de longueur zéro est un comportement actuel susceptible de changer ; traitez-la comme une hypothèse sensible à la version. Les résultats de SQL Server dépendent de ANSI_NULLS, donc sondez la configuration réelle. La coercition booléenne de MySQL s'applique aux contextes booléens et non aux comparaisons = et <>, qui retournent toujours NULL. Les formes compactes de SQLite sont des extensions et ne fonctionneront pas sur des moteurs n'acceptant que IS NOT DISTINCT FROM.
Ces limites sont cruciales car la matrice est une liste de contrôle, pas une garantie. Une requête fonctionnant aujourd'hui sur Oracle pourrait casser après une mise à jour. Le paramètre ANSI_NULLS de SQL Server peut modifier le sens de = et <>, rendant une requête apparemment portable dépendante de la session. La coercition de MySQL peut entraîner des échecs dans une expression CASE même si la requête fonctionne dans un WHERE. Les formes compactes de SQLite ne sont pas portables vers les moteurs standards.
La matrice doit enregistrer le support de IS NOT DISTINCT FROM, syntaxe portable pour l'égalité des NULL en SQL standard. Si un moteur ne le supporte pas, utilisez IS NULL / IS NOT NULL et COALESCE. Ne déduisez pas le comportement d'un moteur à partir d'un autre et ne supposez pas l'interchangeabilité entre chaîne vide et NULL. Les résultats des sondes servent de base pour décider de la syntaxe dans les modules partagés.
L'absence de documentation PostgreSQL dans les sources impose une vérification manuelle avant d'étendre la matrice. La matrice doit être traitée comme un outil de revue de code et mise à jour lors de tout changement de comportement documenté des moteurs. La syntaxe portable IS NULL / IS NOT NULL avec COALESCE reste la seule option indépendante des spécificités de moteur, de session ou de contexte.
Conditions d’application
- Le moteur cible traite-t-il '' IS NULL comme 0 et '' IS NOT NULL comme 1, ou fusionne-t-il la chaîne vide en NULL ?
- Une comparaison telle que 1 = NULL retourne-t-elle NULL, UNKNOWN, ou TRUE/FALSE selon le paramètre de session ?
- Un contexte booléen comme WHERE NOT NULL ou IF(NOT NULL) coerce-t-il NULL en FALSE ou préserve-t-il UNKNOWN ?
- L'ORDER BY du moteur place-t-il les NULL en premier pour ASC et en dernier pour DESC, ou suit-il une autre règle ?
- Le GROUP BY traite-t-il deux NULL comme égaux, et le moteur supporte-t-il IS NOT DISTINCT FROM ?
- La règle de la chaîne vide assimilée à NULL est-elle documentée comme un comportement actuel susceptible de changer ?
- La syntaxe compacte IS et IS NOT est-elle documentée comme une extension plutôt que comme du SQL standard ?
- Les résultats sont-ils affectés par un paramètre de session tel que ANSI_NULLS, et pouvez-vous lire ce paramètre avant le test ?
- Le dispositif de test distingue-t-il un NULL stocké d'une chaîne vide stockée, ou les deux sont-ils confondus ?
- Avez-vous vérifié la matrice avec la documentation PostgreSQL avant d'étendre le support à ce moteur ?
Champ d’application
Ce conseil couvre uniquement la comparaison des NULL et la sémantique des chaînes vides ; il ne traite pas le comptage d'agrégats, le comportement des index ou les performances. Les sources n'incluent pas la documentation PostgreSQL, donc aucun comportement n'est affirmé pour ce moteur. La règle d'Oracle sur les caractères de longueur zéro est sensible à la version et peut changer. Les résultats de SQL Server dépendent du paramètre ANSI_NULLS. La coercition booléenne de MySQL s'applique aux contextes booléens et non aux comparaisons = et <>. Les formes compactes de SQLite sont des extensions non standard.