dus hoe krijg je combinaties van jobtitel en geslacht als het aantal nul is?
We kunnen een cross join gebruiken. Het idee is om eerst een cross join te doen op verschillende waarden van geslacht en titel. Deze resultaten kunnen dan worden buitenste samengevoegd terug naar de werknemer tabel om de rekening te verkrijgen.
dus wat doet deze query echt?
om het schrijven en lezen eenvoudiger te maken, gebruikte ik CTE ‘ s (Common Table Expressions) om deze query aan te maken. Als je niet bekend bent met CTE ‘ s, denk dan aan hen als standpunten voor nu., Opmerking: U kunt meer lezen over CTEs in mijn artikel Inleiding tot Gemeenschappelijke Tabeluitdrukkingen.
We maken twee CTE ‘ s met verschillende JobTitle en Gender waarden uit de Employee table. Door deze tabellen te kruisen kunnen we alle mogelijke combinaties van functietitels en geslacht weergeven.,
Hier is de query die we kunnen gebruiken om verschillende combinaties te maken:
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
zodra u de bovenstaande query bestudeert, zult u zien dat de CROSS JOIN enkel combinaties maakt van twee afzonderlijke afzonderlijke waardenlijsten, kleurgecodeerd blauw en rood, waarvan de resultaten zijn:
nu hoeven we alleen maar de resultaten te nemen en ze te combineren met de samengevatte gegevens die we verkrijgen door de gegevens te groeperen. Opnieuw wordt een CTE gebruikt om de samengevatte gegevens te verzamelen. Een buitenste JOIN wordt dan gebruikt om dit te combineren met alle combinaties van JobTitle en geslacht.,
de reden dat dit werkt is dat we een buitenste JOIN gebruiken om alle resultaten van een tabel te retourneren, ongeacht of ze overeenkomen met een andere tabel. Dit is belangrijk omdat we alle resultaten van de cross join willen opnemen, wat alle combinaties van geslacht en titel is, ongeacht of er daadwerkelijk samengevatte gegevens zijn.
om samen te vatten, hier zijn de stappen die we nemen om dit resultaat te maken:
- krijg een aparte lijst met jobtitels. Dit gebeurt in een CTE.
- krijg een aparte lijst van geslachten. Dit gebeurt in een CTE.,
- maak alle mogelijke combinaties van functietitels en geslachten met behulp van CROSS JOIN.
- Bereken een samenvatting van het aantal werknemers naar functie en geslacht.
- komen overeen met het aantal berekende samenvattingen met een afzonderlijke lijst.
Hier is de laatste query die deze stappen uitvoert:
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;
om het lezen te vergemakkelijken, zijn de twee CTE ‘ s en CROSS JOIN om alle combinaties van JobTitle en Gender te creëren blauw en rood gekleurd. De CTE om de gegevens samen te vatten is groen gekleurd.
merk ook op dat we de SQL COALESCE functie gebruiken om NULL te vervangen door een nulwaarde.,
Hier is het resultaat van de query:
INNER JOIN als CROSS JOIN
naarmate je SQL leert kennen, besef je dat er meestal meer dan één manier is om een query te schrijven. Bijvoorbeeld, er is een manier met behulp van de WHERE clausule om een kruis JOIN gedragen als een innerlijke JOIN.
laten we deze twee tabellen nemen:
stel dat we alle werknemers willen bevragen en hun geboortedatum en achternaam willen tonen. Om dit te doen moeten we de werknemer tafel te relateren aan persoon.,
zoals we hebben gezien kunnen we een cross join schrijven om rijen zo te combineren
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
maar deze query is in dit geval niet al te nuttig, want het geeft 5.791.880 rijen terug!
om de rijcombinaties te beperken zodat de persoonrecords correct worden afgestemd op de werknemersrijen kunnen we een WHERE-clausule gebruiken. Hier is de laatste query:
Hier zijn de eerste 14 rijen van 290:
deze query geeft dezelfde resultaten als een geschreven met een innerlijke JOIN., De query met een interne join is:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
welke query is beter? Als je kijkt naar de query plannen, je zult zien dat ze zijn zeer vergelijkbaar.
Hier is het plan voor de cross join …
Hier is het plan voor de innerlijke join…
zoals u ziet zijn ze identiek. De reden dat ze zo zijn, is dat SQL een declaratieve taal is, wat betekent dat we de DB vertellen welk resultaat we willen, niet noodzakelijkerwijs hoe het te doen. Wanneer we het DBMS voorzien van onze query heeft de optimizer het beste plan samengesteld., In de meeste gevallen zal het hetzelfde zijn voor gelijkwaardige verklaringen.