Buscar este blog

martes, 11 de enero de 2011

Aprendiendo a usar LEFT OUTER JOIN

En esta entrada pretendemos explicar los diferentes resultados obtenidos por distintas construcciones de consultas que, aparentemente, deberían producir el mismo conjunto de resultados.

Así, veremos las diferencias entre filtrar los resultados de una query en la unión (Join) mediante condiciones ON y mediante cláusulas WHERE.


Pongamos por ejemplo las siguientes dos tablas:

Clientes

IdCliente NombreCliente        Direccion                Telefono
--------- -------------------- -------------------- --------------------
1             Juan                       Calle Mayor             666111999
2             Pedro                     Calle de Arriba         NULL
3             Lucas                     Avenida del Puerto   639936369


Pedidos

IdPedido FechaPedido IdCliente Precio
--------- ------------- --------- ----------------------
1             2011-01-11  2            10250
2             2011-01-11  2            3078,5
3             2011-01-11  1            NULL
4             2011-01-11  1            6042


Supongamos ahora que queremos obtener un listado de los clientes con datos de los pedidos que han hecho, como la fecha o el precio.

Para ello usaremos el operador LEFT OUTER JOIN, que nos garantiza que todos los clientes aparecerán en nuestro listado, independientemente de que hayan hecho o no pedidos.

SELECT C.IdCliente, C.NombreCliente, P.IdPedido, P.FechaPedido, P.Precio
FROM Clientes C
LEFT OUTER JOIN Pedidos P ON P.IdCliente = C.IdCliente

El resultado de la consulta es el siguiente:

IdCliente NombreCliente IdPedido FechaPedido Precio
--------- -------------- --------- --------------- --------
1             Juan                3            2011-01-11    NULL
1             Juan                4            2011-01-11    6042
2             Pedro              1            2011-01-11    10250
2             Pedro              2            2011-01-11    3078,5
3             Lucas            NULL       NULL            NULL


Ahora bien, si queremos obtener de esa lista, los registros cuyo precio es nulo, debemos ir con cuidado.

En el caso de haber utilizado un INNER JOIN, habrían aparecido solamente los cuatro primeros registros. Después, para filtrar los registros con precio nulo, podríamos haber usado tanto la condición de la unión (JOIN), como un filtro con cláusula WHERE de manera intercambiable, obteniendo en ambos casos el mismo resultado. Para los dos métodos, el resultado sería solamente el primer registro, ya que el último de ellos no satisfaría la condición de unión, al no existir ningún pedido con su IdCliente.

Sin embargo, no funciona del mismo modo el LEFT OUTER JOIN. Quizás, influenciados por lo que conocemos del INNER JOIN, podríamos tener la tentación de añadir a la condición de la unión que el precio sea nulo, mediante la siguiente consulta:

SELECT C.IdCliente, C.NombreCliente, P.IdPedido, P.FechaPedido, P.Precio
FROM Clientes C
LEFT OUTER JOIN Pedidos P ON P.IdCliente = C.IdCliente AND P.Precio IS NULL

Vemos que aparecería un nuevo registro, que no estaba entre los originados por la consulta inicial:

IdCliente NombreCliente IdPedido FechaPedido Precio
--------- ---------------- --------- -------------- ----------------------
1             Juan                   3            2011-01-11   NULL
2             Pedro                NULL     NULL           NULL
3             Lucas                NULL     NULL           NULL

El primer resultado cumple las dos condiciones de unión impuestas en el LEFT OUTER JOIN. Como para los Clientes 2 y 3, no existen registros en la tabla Pedidos que cumplan esa condición, SQL nos los devuelve con valores nulos para los campos de la tabla Pedidos.

Si lo que de verdad queremos es filtrar del primer conjunto de resultados, aquéllos que cumplen con la condición de que el precio sea nulo, la consulta correcta es la siguiente:

SELECT C.IdCliente, C.NombreCliente, P.IdPedido, P.FechaPedido, P.Precio
FROM Clientes C
LEFT OUTER JOIN Pedidos P ON P.IdCliente = C.IdCliente 
WHERE P.Precio IS NULL

Y esta vez sí, el resultado obtenido es el conjunto de resultados primero, filtrado por aquéllos que cumplen que su precio sea nulo:

IdCliente NombreCliente IdPedido FechaPedido Precio
--------- --------------- ---------- ------------- ----------------------
1             Juan                 3              2011-01-11  NULL
3             Lucas               NULL      NULL           NULL

Así pues, debemos ir con cuidado cuando utilicemos el operador LEFT OUTER JOIN y queramos aplicar condiciones o filtros sobre campos con valores nulos, ya que, en este caso, condiciones y filtros no serán intercambiables, generando distintos conjuntos de resultados en cada caso.

13 comentarios:

  1. tengo un caso similar en una tabla las ventas con canal de venta y otra tabla solo canal de venta, hay meses que algunos canales no tiene venta como hago para ponerle cero en mi query

    ResponderEliminar
    Respuestas
    1. Hola, Luis:

      El problema que planteas es más complejo. No es cuestión de LEFT OUTER JOIN. Necesitas generar una secuencia de meses para que tu query devuelva resultados incluso en los meses en los que no hay ventas.

      Aaron Bertrand propone esta solución (hay que adaptarla al nombre de tus tablas):

      DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

      SELECT @StartDate = '20120101', @EndDate = '20120630';

      ;WITH d(d) AS
      (
      SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
      FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
      n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
      FROM sys.all_objects ORDER BY [object_id] ) AS n
      )
      SELECT
      [Month] = DATENAME(MONTH, d.d),
      [Year] = YEAR(d.d),
      OrderCount = COUNT(o.OrderNumber)
      FROM d LEFT OUTER JOIN dbo.OrderTable AS o
      ON o.OrderDate >= d.d
      AND o.OrderDate < DATEADD(MONTH, 1, d.d)
      GROUP BY d.d
      ORDER BY d.d;

      http://stackoverflow.com/questions/11479918/include-missing-months-in-group-by-query

      Eliminar
  2. man cambiado los campos x mi tabla resulta que es un count ejecutando, lo que necesito si es canal de ventas no tuvo ventas osea no existe registros ponerle cero para mostrar en mi reporting services

    ResponderEliminar
  3. Jaime no seria mejor algo asi DECLARE @mes int,
    @valor int
    SET @valor=0
    SET @mes=1
    SELECT dov.ORGVT,@valor AS total
    FROM dbo.DimOrgVta dov
    WHERE NOT EXISTS
    (SELECT dvs.mes,dvs.OrgVt
    FROM dbo.DocVentas_SAP dvs
    WHERE dvs.orgvt=dov.ORGVT AND dvs.ANNO=2016 AND dvs.mes=@mes
    -- UNION ALL
    -- SELECT dvs.mes,dvs.OrgVt
    -- FROM dbo.DocVentas_SAP dvs
    -- WHERE dvs.orgvt=dov.ORGVT AND dvs.ANNO=2016 AND dvs.mes=3
    )

    ResponderEliminar
  4. Buen día, en caso de tener la siguiente tabla
    CODIGO tiposervicio
    1 AGUA
    1 DESAGUE
    2 AGUA
    2 DESAGUE
    3 AGUA
    4 DESAGUE

    como hago para que mi resultado sea

    CODIGO TIPO SERVICIO
    1 AGUA y DESAGUE
    2 AGUA y DESAGUE
    3 AGUA
    4 DESAGUE

    Para que no se dupliquen los codigos y unir los servicios en un solo registro.

    ResponderEliminar
    Respuestas
    1. Hola:

      Lo que pretendes es concatenar los valores de la columna "tiposervicio" para aquellos registros cuyo código sea el único.
      Para ello tienes que concatenar cadenas de diferentes filas de una misma columna. SQL Server no ofrece una operación trivial que dé solución a este escenario, pero puedes construirte tu propia query para conseguirlo.
      De todas formas, quizás debería plantearte si los datos están correctamente guardados en tu tabla, y si el diseño de ésta es correcto, ya que parece un poco extraño que tengas códigos repetidos.
      Este artículo (en inglés) da las diferentes soluciones para conseguir lo que tu propones: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

      En concreto yo usaría la de FOR XML PATH, así:

      SELECT p1.codigo,
      STUFF( (SELECT ' y ' + nombre
      FROM MiTabla p2
      WHERE p2.codigo = p1.codigo
      ORDER BY nombre
      FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 3, '')
      AS Products
      FROM MiTabla p1
      GROUP BY codigo;

      Saludos!

      Eliminar
  5. Buen Día

    Mi duda con respecto a este tema es la siguiente.

    Independientemente de los resultados que deseamos obtener, como saber cuando emplear LEFT o RIGHT considerando el número de registros por tabla.

    Tabla A = 50000 registros
    Tabla B = 50 registros

    Ninguna de las tablas tiene nulos por lo que en ambos casos trae los mismo resultados.

    Que me recomiendan o que debo tomar en cuenta en la eleccion entre una y otra.

    S2.

    ResponderEliminar
    Respuestas
    1. Hola, Jorge:

      La elección del tipo de JOIN no depende de la existencia o no de nulos en las tablas. Depende del resultado que quieras obtener.

      Por ejemplo, si tengo una tabla clientes y otra pedidos, puedo querer una de estas dos cosas:

      1. Lista de clientes que tenga al menos un pedido. En este caso usaré INNER JOIN, porque tiene que haber registros en las dos tablas.

      2. Lista de TODOS los clientes y el número de pedidos de cada cliente, aunque no tengan ningún pedido. En este caso necesitaré un LEFT o RIGHT JOIN (normalmente se usa LEFT, casi nunca el RIGHT) para decirle a SQL que me devuelva todos los registros de la tabla Clientes y me ponga un NULL en las columnas de la tabla Pedidos que estoy pidiendo, cuando no existan pedidos para el cliente.

      Espero haber aclarado tus dudas.

      Saludos,

      Jaime

      Eliminar
  6. Jaime

    Muchas Gracias por su respuesta con base a ella entiendo que mi pregunta ha sido mal planteada.

    Ya que tengo una consulta en la que si varia el tiempo de respuesta dependiendo si uso LEFT o RIGHT

    Cabe mencionar que mi tabla A contiene un mayor # de registros que mi tabla B, de la cual solo extraigo una descripción ya que es una tabla de catalogo, entonces si eligo A LEFT B ON (a.id=b.id) demora más en responder que si aplico A RIGHT B ON (a.id=b.id).

    Lo unico que se me ocurre que es un menor tiempo de respuesta debido a que B tiene menos registros.

    Podría orientarme por favor, estaré al pendiente de su respuesta.

    Muchas gracias.

    S2.

    ResponderEliminar
    Respuestas
    1. Hola Jorge:

      Claro, son consultas diferentes.

      Con LEFT le estás diciendo "dame todos los registros de la tabla A JOIN a todos los registros de la tabla B cuyo id sea igual al id de A. Además, dame los registros de A que no tengan registros en B, poniendo un NULL en las columnas de B".

      Con RIGHT le estás diciendo justo todo lo contrario: "Dame todos los registros de B JOIN a todos los registros de la A cuyo id sea igual al id de B. Además, dame los registros de B que no tengan registros en A para el mismo id, poniendo un NULL en las columnas de B".

      Por ejemplo, si ningún registro de A tuviese el mismo id que ningún registro de B, con LEFT obtendrías 5000 filas devueltas. Con RIGHT solamente obtendrías 50.

      A RIGHT B es lo mismo que B LEFT A.
      Te aconsejo usar siempre LEFT y simplemente cambiar el orden de las tablas. Cuando te acostumbras, es más fácil de leer.

      Jaime.

      Eliminar
  7. Estimado Jaime, veo que la haces de goma con SQL.
    Estoy hace días intentando hacer un left join en SQL server, pero no da todos los resultados de la tabla derecha, siempre hace como un inner join.
    Esta consulta me devuelve 1020 registros
    SELECT A1_GRPVEN, A1_END, A1_COD AS CLIENTE, A1_NOME AS RAZON_SOCIA, A1_LOJA
    FROM SA1U00
    WHERE A1_TIPO!=3 AND SA1U00.D_E_L_E_T_ <> '*' AND A1_FILIAL='U2'
    Mientras que esta me devuelve 51
    SELECT A1_GRPVEN, A1_END, A1_COD AS CLIENTE, A1_NOME AS RAZON_SOCIA, A1_LOJA
    ,D2_CUSFF2, D2_CUSTO1, D2_CUSTO2, D2_FILIAL AS FILIAL, D2_EMISSAO AS FECHA ,D2_COD AS PRODUCTO
    FROM SA1U00
    LEFT JOIN
    SD2U00
    LEFT JOIN SF2U00 ON D2_SERIE = F2_SERIE AND D2_DOC = F2_DOC AND D2_ESPECIE = F2_ESPECIE AND D2_EMISSAO = F2_EMISSAO AND D2_CLIENTE= F2_CLIENTE AND D2_LOJA = F2_LOJA AND F2_FILIAL=D2_FILIAL AND SF2U00.D_E_L_E_T_ <> '*'
    on D2_CLIENTE = A1_COD
    WHERE
    F2_EMISSAO BETWEEN @cDFecha and @cHFecha
    AND F2_NATUREZ BETWEEN @cDCanal AND @cHCanal
    AND SD2U00.D_E_L_E_T_ <>'*' AND D2_FILIAL='U2U2'
    AND F2_ESPECIE IN ('NF')
    AND A1_TIPO!=3 AND SA1U00.D_E_L_E_T_ <> '*' AND A1_FILIAL='U2'
    AND D2_LOJA = A1_LOJA
    AND D2_REMITO = ''
    Entiendo que me debería devolver los 1020 con sus null correspondientes, no?
    Que hago mal?

    ResponderEliminar
    Respuestas
    1. Hola, Lista 11:

      El filtro de las dos consultas es diferente (cláusula WHERE).
      Sin conocer las tablas es difícil asegurarlo, pero es más que probable que estés filtrando más registros en la segunda consulta.

      Si no pones cláusula WHERE o si pones en la segunda consulta la misma que en la primera sí que deberías obtener el mismo número de registros.

      Espero haberte ayudado.

      Saludos,

      Eliminar
    2. Creo que me ayudaste, si en tus ejemplos de left join le agregara un Where fechaPedido between A y B, ese resultado ya no me traería los NULL, dejándome sólo los clientes que tuvieron pedidos en ese período.
      Si es así, eso es lo que me está sucediendo, muchas gracias.

      Eliminar