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.
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:
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.
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
ResponderEliminarHola, Luis:
EliminarEl 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
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
ResponderEliminarJaime no seria mejor algo asi DECLARE @mes int,
ResponderEliminar@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
)
Buen día, en caso de tener la siguiente tabla
ResponderEliminarCODIGO 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.
Hola:
EliminarLo 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!
Buen Día
ResponderEliminarMi 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.
Hola, Jorge:
EliminarLa 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
Jaime
ResponderEliminarMuchas 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.
Hola Jorge:
EliminarClaro, 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.
Estimado Jaime, veo que la haces de goma con SQL.
ResponderEliminarEstoy 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?
Hola, Lista 11:
EliminarEl 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,
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.
EliminarSi es así, eso es lo que me está sucediendo, muchas gracias.
Amigo tengo esta consulta
ResponderEliminarSelect * from posición cpos left router join agrenme cagg on cpos.contracid=cagg.contracid or (cpos.contraid is null and cagg.contracid is null)
De que manera la mejoraría o atu parecer esta bien así me la dejaron para mejorarlo
interesante articulo de https://thedevelopmentstages.com/como-utilizar-el-operador-not-in-en-sentencias-sql/
ResponderEliminar