tutorial - ¿Cuál es la diferencia entre LATERAL y una subconsulta en PostgreSQL?

tipos de subconsultas sql

Question

Desde que Postgres salió con la capacidad de hacer uniones LATERAL , lo he estado leyendo, ya que actualmente hago volcados de datos complejos para mi equipo con muchas subconsultas ineficientes que hacen que la consulta general tome cuatro minutos o más.

Entiendo que las uniones LATERAL pueden ayudarme, pero incluso después de leer artículos como este de Heap Analytics, todavía no lo sigo.

¿Cuál es el caso de uso para una unión LATERAL ? ¿Cuál es la diferencia entre una unión LATERAL y una subconsulta?

Answer 1

Más como una subconsulta correlacionada

Una unión LATERAL (Postgres 9.3 o posterior) es más como una subconsulta correlacionada , no una subconsulta simple. Como señaló Andomar , una función o subconsulta a la derecha de una unión LATERAL debe evaluarse una vez por cada fila a la izquierda de la misma, al igual que una subconsulta correlacionada , mientras que una subconsulta simple (expresión de tabla) se evalúa solo una vez . (Sin embargo, el planificador de consultas tiene formas de optimizar el rendimiento).
Esta respuesta relacionada tiene ejemplos de código para ambos lado a lado, resolviendo el mismo problema:

• Optimice la consulta GROUP BY para recuperar la última fila por usuario

Para devolver más de una columna , una unión LATERAL suele ser más simple, limpia y rápida.
Además, recuerde que el equivalente de una subconsulta correlacionada es LEFT JOIN LATERAL ... ON true :

• Llama a una función set-return con un argumento de matriz varias veces

Lea el manual en LATERAL

Es más autoritario que cualquier cosa que vamos a poner aquí en respuestas:

• https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

Cosas que una subconsulta no puede hacer

Hay cosas que puede hacer una unión LATERAL , pero una subconsulta (correlacionada) no puede (fácilmente). Una subconsulta correlacionada solo puede devolver un valor único, no múltiples columnas y no múltiples filas, con la excepción de las llamadas a funciones desnudas (que multiplican las filas de resultados si devuelven varias filas). Pero incluso ciertas funciones de devolución de conjuntos solo están permitidas en la cláusula FROM . Como unnest() con múltiples parámetros en Postgres 9.4 o posterior. El manual:

Esto solo está permitido en la cláusula FROM ;

Entonces esto funciona, pero no se puede reemplazar fácilmente con una subconsulta:

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

La coma ( , ) en la cláusula FROM es una notación corta para CROSS JOIN .
LATERAL se supone automáticamente para las funciones de tabla.
Más sobre el caso especial de UNNEST( array_expression [, ... ] ) :

• ¿Cómo declara que una función set-return-function solo se permita en la cláusula FROM?

Establecer funciones de retorno en la lista SELECT

También puede usar funciones de retorno de conjuntos como unnest() en la lista SELECT directamente. Esto solía exhibir un comportamiento sorprendente con más de una función en la misma lista SELECT hasta Postgres 9.6. Pero finalmente se ha desinfectado con Postgres 10 y ahora es una alternativa válida (incluso si no es SQL estándar). Ver:

• ¿Cuál es el comportamiento esperado para múltiples funciones de retorno de conjuntos en la cláusula SELECT?

Sobre la base del ejemplo anterior:

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

Comparación:

dbfiddle para la página 9.6 here
dbfiddle para pg 10 here

Aclarar información errónea

El manual:

Para los tipos de combinación INNER y OUTER , se debe especificar una condición de combinación, concretamente una de NATURAL , ON join_condition o USING ( join_column [, ...]). Vea a continuación el significado.
Para CROSS JOIN , ninguna de estas cláusulas puede aparecer.

Estas dos consultas son válidas (incluso si no son particularmente útiles):

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;

Si bien este no es:

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

Es por eso que el ejemplo de código de @ Andomar es correcto ( CROSS JOIN no requiere una condición de unión) y @Attila''s es válido.

Answer 2

La diferencia entre una unión no lateral y una lateral reside en si puede mirar a la fila de la tabla de la izquierda. Por ejemplo:

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

Esta "mirada hacia afuera" significa que la subconsulta debe evaluarse más de una vez. Después de todo, t1.col1 puede asumir muchos valores.

Por el contrario, la subconsulta después de una unión no lateral se puede evaluar una vez:

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

Como se requiere sin lateral , la consulta interna no depende de ninguna manera de la consulta externa. Una consulta lateral es un ejemplo de una consulta correlated , debido a su relación con filas fuera de la consulta misma.

Answer 3

Primero, Lateral y Cross Apply es lo mismo . Por lo tanto, también puede leer sobre Cross Apply. Dado que se implementó en SQL Server durante siglos, encontrará más información al respecto que Lateral.

En segundo lugar, según tengo entendido , no hay nada que no pueda hacer usando subconsulta en lugar de usar lateral. Pero:

Considere la siguiente consulta.

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 

Puede usar lateral en esta condición.

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

En esta consulta no puede usar la unión normal, debido a la cláusula de límite. La aplicación lateral o cruzada se puede usar cuando no existe una condición de unión simple .

Hay más usos para la aplicación lateral o cruzada, pero este es el más común que encontré.

Answer 4

Una cosa que nadie ha señalado es que puede usar consultas LATERAL para aplicar una función definida por el usuario en cada fila seleccionada.

Por ejemplo:

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);

Esa es la única forma en que sé cómo hacer este tipo de cosas en PostgreSQL.