entonces, ¿cómo obtiene combinaciones de JobTitle y GÉNERO Cuando el recuento es cero?
Podemos usar una combinación cruzada. La idea es primero hacer una unión cruzada en valores distintos de género y título. Estos resultados se pueden unir de nuevo a la tabla de empleados para obtener la cuenta.
entonces, ¿qué hace realmente esta consulta?
para simplificar la escritura y la lectura, utilicé CTE (Common Table Expressions) para crear esta consulta. Si no está familiarizado con los CTE, piense en ellos como puntos de vista por ahora., Nota: puede leer más sobre CTEs en mi artículo Introducción a expresiones de tabla comunes.
creamos dos CTE de distintos valores de JobTitle y de género a partir de la tabla de empleados. Al unir estas tablas podemos mostrar todas las combinaciones posibles de títulos de trabajo y género.,
Aquí está la consulta que podemos usar para crear combinaciones distintas:
WITH cteJobTitle (JobTitle)AS (SELECT DISTINCT JobTitle FROM HumanResources.Employee),cteGender (Gender)AS (SELECT DISTINCT Gender FROM HumanResources.Employee)SELECT J.JobTitle, G.GenderFROM cteJobTitle AS J CROSS JOIN cteGender AS GORDER BY J.JobTitle
Una vez que estudie la consulta anterior verá que la combinación cruzada solo está creando combinaciones a partir de dos listas de valores distintas separadas, codificadas por colores azul y rojo, cuyos resultados son:
Ahora, todo lo que necesitamos hacer es tomar los resultados y combinarlos con datos resumidos obtenemos agrupando los datos. Una vez más se utiliza un CTE para recoger los datos resumidos. Una unión externa se utiliza para combinar esto con todas las combinaciones de JobTitle y Gender.,
la razón por la que esto funciona es que estamos usando una unión externa para devolver todos los resultados de una tabla independientemente de si coinciden con otra. Esto es importante, ya que queremos incluir todos los resultados de la combinación cruzada, que es todas las combinaciones de género y título, independientemente de si realmente hay datos resumidos.
para resumir, aquí están los pasos que estamos tomando para crear este resultado:
- Obtener una lista distinta de títulos de trabajo. Esto sucede en un CTE.
- Obtener una lista distinta de géneros. Esto sucede en un CTE.,
- cree todas las combinaciones posibles de títulos de trabajo y géneros utilizando la combinación cruzada.
- Calcule un recuento resumido de empleados por título de trabajo y género.
- haga coincidir el recuento de resumen calculado con una lista distinta.
Aquí está la consulta final que realiza estos pasos:
WITH cteJobTitle (JobTitle)AS (SELECT DISTINCT JobTitle FROM HumanResources.Employee), cteGender (Gender)AS (SELECT DISTINCT Gender FROM HumanResources.Employee), cteCounts (JobTitle, Gender, NumberEmployees)AS (SELECT JobTitle, Gender, COUNT(1) AS NumberEmployees FROM HumanResources.Employee GROUP BY JobTitle, Gender)SELECT J.JobTitle, G.Gender, COALESCE (C.NumberEmployees, 0) as NumberEmployeesFROM cteJobTitle AS J CROSS JOIN cteGender AS G LEFT OUTER JOIN cteCounts AS C ON C.JobTitle = J.JobTitle AND C.Gender = G.GenderORDER BY J.JobTitle, G.Gender;
para que sea más fácil de leer, los dos CTE y CROSS JOIN para crear todas las combinaciones de JobTitle y Gender son de color azul y rojo. El CTE para resumir los datos es de color verde.
también observe que usamos la función SQL COALESCE para reemplazar NULL por un valor cero.,
Aquí está el resultado de la consulta:
INNER JOIN as CROSS JOIN
a medida que conoces SQL te das cuenta de que generalmente hay más de una forma de escribir una consulta. Por ejemplo, hay una manera de usar la cláusula WHERE para que una unión cruzada se comporte como una unión interior.
tomemos estas dos tablas:
supongamos que deseamos consultar a todos los empleados y mostrar su fecha de nacimiento y apellidos. Para hacer esto tenemos que relacionar la tabla del empleado con la persona.,
como hemos visto podemos escribir una combinación cruzada para combinar filas de la siguiente manera
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
pero esta consulta no es demasiado útil en este caso, ya que devuelve 5,791,880 filas!
para limitar las combinaciones de filas para que los registros de personas coincidan correctamente con las filas de empleados, podemos usar una cláusula WHERE. Aquí está la consulta final:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
Aquí están las primeras 14 filas de 290:
esta consulta devuelve los mismos resultados que una escrita con una combinación interna., La consulta que usa una combinación interna es:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
¿Qué consulta es mejor? Si nos fijamos en los planes de consulta, verá que son muy similares.
Aquí está el plan de la cruz unirse a…
Aquí es el plan para el inner join…
Como se puede ver que son idénticos. La razón por la que son así, es que SQL es un lenguaje declarativo, lo que significa que le decimos a la base de datos qué resultado queremos, no necesariamente cómo hacerlo. Cuando proporcionamos el DBMS con nuestra consulta el optimizador armar el mejor plan., En la mayoría de los casos será el mismo para las declaraciones equivalentes.