Quelle est la différence entre le LATÉRAL et une sous-requête dans PostgreSQL?

Depuis Postgres est sorti avec la capacité de faire LATERAL jointures, j'ai lu sur elle, depuis que je fais de données complexes dumps pour mon équipe avec beaucoup de l'inefficacité des sous-requêtes qui font de la requête globale de prendre quatre minutes ou plus.

Je comprends que LATERAL rejoint peut-être pouvoir m'aider, mais même après avoir lu des articles comme cette une de Tas Analytics, je n'ai toujours pas tout suivre.

Ce qui est le cas d'utilisation pour un LATERAL rejoindre? Quelle est la différence entre un LATERAL rejoindre et d'une sous-requête?

InformationsquelleAutor jdotjdot | 2015-02-16
  1. 122

    Plus comme un corrélation sous-requête

    Un LATERAL rejoindre (Postgresql 9.3 ou une version ultérieure) est plus comme un sous-requête en corrélation, pas un simple sous-requête. Comme Andomar souligné, une fonction ou une sous-requête pour le droit d'un LATERAL de jointure doit être évaluée une fois pour chaque ligne de gauche de celui - ci- tout comme un corrélation sous-requête - tout en un simple sous-requête (expression de table) est évalué une fois seulement. (Le planificateur de requête a des moyens pour optimiser les performances, soit, si.)

    Cette réponse a des exemples de code pour les deux côte à côte, résoudre le même problème:

    Pour le retour plus d'une colonne, un LATERAL jointure est généralement plus simple, plus propre et plus rapide.

    Aussi, n'oubliez pas que l'équivalent d'une sous-requête corrélée est LEFT JOIN LATERAL ... ON true:

    Lire le manuel sur LATERAL

    Il est plus autoritaire que tout ce que nous allons mettre dans les réponses ici:

    Les choses d'une sous-requête ne peut pas faire

    Il sont choses qu'un LATERAL rejoindre ne peut, mais un (corrélation) sous-requête ne peut pas (facilement). Une sous-requête en corrélation ne peut retourner qu'une seule valeur, et non pas de plusieurs colonnes et plusieurs lignes à l'exception de nu appels de fonction (qui multiplient les lignes de résultats si ils retourner plusieurs lignes). Mais même certains set‑retour fonctions ne sont autorisés que dans la FROM clause. Comme unnest() avec plusieurs paramètres dans Postgresql 9.4 ou plus tard. Le manuel de:

    C'est uniquement autorisé dans le FROM clause;

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

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

    La virgule (,) dans le FROM clause est à court de notation pour les CROSS JOIN.

    LATERAL est supposé automatiquement pour les fonctions de table.

    Plus sur le cas particulier de UNNEST( array_expression [, ... ] ):

    Jeu-de retour des fonctions dans le SELECT liste

    Vous pouvez également utiliser le set-de retour des fonctions comme unnest() dans le SELECT liste directement. Cette figure comportement surprenant à plus d'une fonction dans le même SELECT liste jusqu'à Postgres 9.6. Mais il a finalement été désinfectés avec Postgres 10 et est une alternative valable maintenant (même si c'est pas le standard SQL). Voir:

    Appuyant sur l'exemple ci-dessus:

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

    Comparaison:

    dbfiddle pour pg 9.6 ici

    dbfiddle pour pg 10 ici

    Clarifier la désinformation

    Le manuel de:

    Pour la INNER et OUTER types de jointure, une condition de jointure doit être
    spécifiés, à savoir exactement un de NATURAL, ON join_condition,
    ou USING (join_column [, ...]). Voir ci-dessous pour la signification.

    Pour CROSS JOIN, aucune de ces clauses peuvent apparaître.

    De sorte que ces deux requêtes sont valables (même si pas particulièrement utile):

    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;

    Tandis 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 @Andomar de l' exemple de code est correct (le CROSS JOIN ne nécessite pas de condition de jointure) et @Attila du est n'est pas valide.

    • Il y a des choses qu'une sous-requête peut ne LATÉRAL de REJOINDRE ne peut pas faire. Comme les fonctions de la fenêtre. Comme ici
    • Je ne pouvais pas trouver toutes les sous-requêtes corrélées dans le lien. Mais j'ai ajouté une autre réponse à démontrer une fonction de fenêtre dans un LATERAL sous-requête: gis.stackexchange.com/a/230070/7244
    • Plus propre et plus rapide? Comme beaucoup plus rapide dans certains cas. J'ai eu une requête qui est allé de quelques jours à quelques secondes après le passage LATÉRAL.
    InformationsquelleAutor Erwin Brandstetter
  2. 31

    La différence entre un non-lateral et un lateral joindre se trouve dans le fait que vous pouvez regarder pour la main gauche de la table en ligne. Par exemple:

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

    Cet "extérieur" signifie que la sous-requête doit être évalué plus d'une fois. Après tout, t1.col1 peut prendre beaucoup de valeurs.

    En revanche, la sous-requête après un non-lateral jointure 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

    Que nécessaire sans lateral, la requête interne ne dépendent en aucune manière de la requête externe. Un lateral requête est un exemple de correlated requête, en raison de sa relation avec les lignes à l'extérieur de la requête elle-même.

    • C'est le plus propre explication de latéral rejoindre.
    InformationsquelleAutor Andomar
  3. 9

    D'abord, Et transversales Appliquer est la même chose. Par conséquent vous pouvez également lire sur la Croix s'Appliquent. Depuis qu'il a été mis en œuvre dans SQL Server pour les âges, vous trouverez plus d'informations à ce sujet alors Latérale.

    Deuxième, selon ma compréhension, il n'y a rien que vous pouvez faire à l'aide de sous-requête au lieu d'utiliser latérale. Mais:

    Envisager la suite de la requête.

    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érale 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 des rejoindre, en raison de la limite de la clause.
    Latérale ou de la Croix-Appliquer peut être utilisé quand il n'est pas simple condition de jointure.

    Il y a plus d'utilisations pour les latérales ou de la croix appliquer, mais c'est plus commun que j'ai trouvé.

    • Exactement, je me demande pourquoi PostgreSQL utilise lateral au lieu de apply. Peut-être que Microsoft a breveté la syntaxe?
    • Autant que je sache lateral est dans le standard SQL mais apply ne l'est pas.
    • Le LEFT JOIN exige une condition de jointure. Faire ON TRUE sauf si vous souhaitez restreindre quelque sorte.
    • Erwin est à droite, vous obtiendrez une erreur, sauf si vous utilisez un cross join ou un on condition
    • Poussé par cette désinformation, j'ai ajouté une autre réponse à préciser.
    • La mise à jour de la requête est incorrecte nouveau. Vous ne pouvez pas joindre sur X.Fk1 car il n'est pas dans le résultat de la sous-requête. Si vous incluez Fk1 dans la sous-requête, il sera formellement correct, mais présentent toujours exotiques comportement. Il sélectionne un arbitraire ligne de B et ne rejoint-il à A si Fk1 correspond à A.PK.
    • Pourriez-vous le vérifier?
    • Il est maintenant valide.
    • and Limit 1? C'est qu'une faute de frappe ou un valide construire avec lequel je ne suis pas familier?

    InformationsquelleAutor Atilla Ozgur
  4. 1

    Une chose que personne n'a souligné, c'est que vous pouvez utiliser LATERAL requêtes pour appliquer une fonction définie par l'utilisateur sur chaque ligne sélectionnée.

    Par exemple:

    CREATE OR REPLACE FUNCTION delete_company(companyId varchar(255))
RETURNS void AS $$
    BEGIN
        DELETE FROM company_settings WHERE "company_id"=company_id;
        DELETE FROM users WHERE "company_id"=companyId;
        DELETE FROM companies WHERE id=companyId;
    END; 
$$ LANGUAGE plpgsql;

SELECT * FROM (
    SELECT id, name, created_at FROM companies WHERE created_at < '2018-01-01'
) c, LATERAL delete_company(c.id);

    C'est la seule façon que je sais comment faire ce genre de chose dans PostgreSQL.

    InformationsquelleAutor Theodore R. Smith