alors, comment obtenez-vous des combinaisons de JobTitle et de Gender lorsque le nombre est nul?
nous pouvons utiliser une jointure croisée. L’idée est d’abord de faire une jointure croisée sur des valeurs distinctes de genre et le titre. Ces résultats peuvent ensuite être joints à la table des employés pour obtenir le compte.
alors, que fait vraiment cette requête?
pour simplifier l’écriture et la lecture, j’ai utilisé CTE (Common Table Expressions) pour créer cette requête. Si vous n’êtes pas familier avec les CTE, considérez-les comme des vues pour l’instant., REMARQUE: Vous pouvez en savoir plus sur CTEs dans mon article Introduction aux Expressions de Table courantes.
Nous créons deux CTE de valeurs distinctes JobTitle et Gender à partir de la table Employee. En croisant ces tableaux, nous pouvons afficher toutes les combinaisons possibles de titres D’emploi et de sexe.,
Voici la requête que nous pouvons utiliser pour créer des combinaisons distinctes:
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
Une fois que vous étudiez la requête ci-dessus, vous verrez que la jointure croisée crée simplement des combinaisons à partir de deux listes de valeurs distinctes distinctes, bleu et rouge, dont les résultats sont:
onnées que nous obtenons en regroupant les données. Encore une fois, un CTE est utilisé pour collecter les données résumées. Une jointure externe est ensuite utilisée pour combiner cela avec toutes les combinaisons de JobTitle et Gender.,
la raison pour laquelle cela fonctionne est que nous utilisons une jointure externe pour renvoyer tous les résultats d’une table, qu’ils correspondent ou non à une autre. Ceci est important car nous voulons inclure tous les résultats de la jointure croisée, qui est toutes les combinaisons de genre et de titre, qu’il y ait ou non des données résumées.
pour résumer, voici les étapes que nous prenons pour créer ce résultat:
- obtenez une liste distincte de JobTitles. Cela se produit dans un CTE.
- obtenez une liste distincte des genres. Cela se produit dans un CTE.,
- créez toutes les combinaisons possibles de titres de tâches et de genres à l’aide de la jointure croisée.
- calculez un nombre récapitulatif d’employés par titre D’emploi et par sexe.
- faites correspondre le nombre récapitulatif calculé avec une liste distincte.
Voici la requête finale qui accomplit ces étapes:
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;
pour faciliter la lecture, les deux CTE et CROSS JOIN pour créer toutes les combinaisons de JobTitle et Gender sont colorés en bleu et rouge. Le CTE pour résumer les données est de couleur verte.
notez également que nous utilisons la fonction SQL COALESCE pour remplacer NULL par une valeur nulle.,
Voici le résultat de la requête:
INNER JOIN comme CROSS JOIN
Comme vous apprenez à connaître SQL, vous réalisez qu’il y a plus d’une façon d’écrire une requête. Par exemple, il existe un moyen d’utiliser la clause WHERE pour qu’une jointure croisée se comporte comme une jointure interne.
prenons ces deux tableaux:
supposons que nous souhaitions interroger tous les employés et montrer leur date de naissance et leur nom de famille. Pour ce faire, nous devons relier la table des employés à la personne.,
Comme nous l’avons vu, nous pouvons écrire une jointure croisée de combiner des lignes comme
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
Mais cette requête n’est pas trop utile dans ce cas, car il renvoie 5,791,880 lignes!
pour limiter les combinaisons de lignes afin que les enregistrements de la personne soient correctement appariés aux lignes de l’employé, nous pouvons utiliser une clause WHERE. Voici la dernière question:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
Voici les 14 premières lignes de 290:
Cette requête renvoie les mêmes résultats que l’on écrit avec une JOINTURE INTERNE., La requête utilisant une jointure interne est:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
quelle requête est la meilleure? Si vous regardez les plans de requête, vous verrez qu’elles sont très similaires.
Voici le plan de la jointure croisée…
Voici le plan de la jointure interne…
Comme vous le voyez, ils sont identiques. La raison pour laquelle ils le sont, c’est que SQL est un langage déclaratif, ce qui signifie que nous disons à la base de données quel résultat nous voulons, pas nécessairement comment le faire. Lorsque nous fournissons au SGBD notre requête, l’optimiseur a mis en place le meilleur plan., Dans la plupart des cas, il en sera de même pour les déclarations équivalentes.