Quelle est la différence entre LATERAL et une sous-requête dans PostgreSQL?

Depuis que Postgres a créé la possibilité de faire des jointures LATERAL , je suis en train de le lire, car je fais actuellement des sauvegardes de données complexes pour mon équipe avec beaucoup de sous-requêtes inefficaces qui rendent la requête globale de quatre minutes ou plus.

Je comprends que les jointures LATERAL peuvent être en mesure de m’aider, mais même après avoir lu des articles comme celui-ci de Heap Analytics, je ne suis toujours pas tout à fait.

Quel est le cas d’utilisation d’une jointure LATERAL ? Quelle est la différence entre une jointure LATERAL et une sous-requête?

Plus comme une sous-requête corrélée

Une jointure LATERAL (Postgres 9.3+) ressemble plus à une sous – requête corrélée qu’à une sous-requête simple. Comme @Andomar l’a fait remarquer , une fonction ou une sous-requête à droite d’une jointure LATERAL doit généralement être évaluée plusieurs fois – une fois pour chaque ligne à gauche de la jointure LATERAL – comme une sous-requête corrélée – évalué une seule fois . (Le planificateur de requêtes a toutefois des moyens d’optimiser les performances de l’un ou de l’autre.)
Cette réponse connexe a des exemples de code pour les deux côte à côte, résolvant le même problème:

  • Optimiser la requête GROUP BY pour récupérer le dernier enregistrement par utilisateur

Pour renvoyer plus d’une colonne , une jointure LATERAL est généralement plus simple, plus propre et plus rapide. Rappelez-vous également que l’équivalent d’une sous-requête corrélée est LEFT JOIN LATERAL ... ON true :

  • Appeler une fonction set-return avec un argument de tableau plusieurs fois

Lire le manuel d’utilisation sur LATERAL

Il fait plus autorité que tout ce que nous allons apporter ici:

Les choses qu’une sous-requête ne peut pas faire

Il y a des choses qu’une jointure LATERAL peut faire, mais une sous-requête (corrélée) ne peut pas (facilement). Une sous-requête corrélée ne peut renvoyer qu’une seule valeur, et non plusieurs colonnes et pas plusieurs lignes, à l’exception des appels de fonction nue (qui multiplient les lignes de résultat si elles renvoient plusieurs lignes). Mais même certaines fonctions de retour d’ensemble ne sont autorisées que dans la clause FROM . Comme le nouveau unnest() avec plusieurs parameters dans Postgres 9.4. Le manuel:

Ceci est uniquement autorisé dans la clause FROM ;

Cela fonctionne donc, mais ne peut pas être facilement remplacé par une sous-requête:

 CREATE TABLE tbl (a1 int[], a2 int[]); SELECT * FROM tbl t, unnest(t.a1, t.a2) u(elem1, elem2); -- implicit LATERAL 

(La virgule ( , ) dans la clause FROM est une notation courte pour CROSS JOIN .
LATERAL est supposé automatiquement pour les fonctions de la table.)

Plus d’informations sur le cas particulier de UNNEST( array_expression [, ... ] ) sous cette dernière question sur dba.SE:

  • Comment déclarer une fonction set-return uniquement autorisée dans la clause FROM?

Fonctions de renvoi dans la liste SELECT

Vous pouvez également utiliser les fonctions de retour de unnest() comme unnest() directement dans la liste SELECT . Cela montrait un comportement surprenant avec plus d’une instance dans la même liste SELECT jusqu’à Postgres 9.6. Mais il a finalement été assaini avec Postgres 10 et est une alternative valide maintenant (même si ce n’est pas du SQL standard).
S’appuyant sur l’exemple ci-dessus:

 SELECT *, unnest(t.a1) AS elem1, unnest(t.a2) AS elem2 FROM tbl t; 

Comparaison:

dbfiddle pour la pg 9.6 ici
dbfiddle pour pg 10 ici

Clarifier la désinformation

Le manuel clarifie les informations trompeuses ici:

Pour les types de jointure INNER et OUTER , une condition de jointure doit être spécifiée, à savoir exactement une de NATURAL , ON join_condition ou USING ( join_column [, …]). Voir ci-dessous pour la signification.
Pour CROSS JOIN , aucune de ces clauses ne peut apparaître.

Ces deux requêtes sont donc valides (même si elles ne sont pas particulièrement utiles):

 SELECT * FROM tbl t LEFT JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t ON TRUE ; SELECT * FROM tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t; 

Alors que celui-ci n’est pas:

 SELECT * FROM tbl t LEFT JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t; 

C’est pourquoi l’ exemple de code de @ Andomar est correct (le CROSS JOIN ne nécessite pas de condition de jointure) et le nom de @ Attila est invalide.

La différence entre une jointure non lateral et une jointure lateral réside dans le fait de savoir si vous pouvez regarder la rangée de la table de gauche. Par exemple:

 select * from table1 t1 cross join lateral ( select * from t2 where t1.col1 = t2.col1 -- Only allowed because of lateral ) sub 

Cette “recherche extérieure” signifie que la sous-requête doit être évaluée plus d’une fois. Après tout, t1.col1 peut prendre plusieurs valeurs.

En revanche, la sous-requête après une jointure non lateral peut être évaluée une fois:

 select * from table1 t1 cross join ( select * from t2 where t2.col1 = 42 -- No reference to outer query ) sub 

Comme cela est requirejs sans lateral , la requête interne ne dépend en aucune façon de la requête externe. Une requête lateral est un exemple de requête correlated , en raison de sa relation avec les lignes situées en dehors de la requête elle-même.

Tout d’abord, latéral et croix s’appliquent est la même chose . Par conséquent, vous pouvez également lire à propos de Cross Apply. Comme il a été implémenté depuis longtemps dans SQL Server, vous trouverez plus d’informations à ce sujet, puis latéral.

Deuxièmement, selon ma compréhension , il n’ya rien à faire en utilisant la sous-requête au lieu d’utiliser latéralement. Mais:

Envisagez la requête suivante.

 Select A.* , (Select B.Column1 from B where B.Fk1 = A.PK and Limit 1) , (Select B.Column2 from B where B.Fk1 = A.PK and Limit 1) FROM A 

Vous pouvez utiliser latéral dans cette condition.

 Select A.* , x.Column1 , x.Column2 FROM A LEFT JOIN LATERAL ( Select B.Column1,B.Column2,B.Fk1 from B Limit 1 ) x ON X.Fk1 = A.PK 

Dans cette requête, vous ne pouvez pas utiliser la jointure normale en raison de la clause limit. L’application latérale ou croisée peut être utilisée lorsqu’il n’y a pas de condition de jointure simple .

Il y a plus d’utilisations pour l’application latérale ou croisée mais c’est le plus commun que j’ai trouvé.