Quindi come ottieni combinazioni di JobTitle e Gender quando il conteggio è zero?
Possiamo usare un cross join. L’idea è di fare prima un cross join su valori distinti di genere e titolo. Questi risultati possono quindi essere uniti esternamente alla tabella dei dipendenti per ottenere l’account.
Quindi cosa fa davvero questa query?
Per semplificare la scrittura e la lettura, ho usato CTE (Common Table Expressions) per creare questa query. Se non si ha familiarità con CTE di, pensare a loro come viste per ora., Nota: puoi leggere di più su CTE nel mio articolo Introduzione alle espressioni di tabella comuni.
Creiamo due CTE di valori JobTitle e Gender distinti dalla tabella Employee. Incrociando queste tabelle possiamo visualizzare tutte le possibili combinazioni di titoli di lavoro e sesso.,
questa è la query che si possono utilizzare per creare combinazioni distinte:
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 Volta che studio la query di cui sopra, vedrete il CROSS JOIN è solo la creazione di combinazioni di due distinti distinti valori di elenchi di colore blu e rosso, i cui risultati sono:
Ora, tutto quello che dobbiamo fare è prendere i risultati e li combinano con il riepilogo dei dati si ottiene raggruppando i dati. Anche in questo caso un CTE viene utilizzato per raccogliere i dati riassunti. Un JOIN ESTERNO viene quindi utilizzato per combinare questo con tutte le combinazioni di JobTitle e Gender.,
Il motivo per cui funziona è che stiamo usando un JOIN ESTERNO per restituire tutti i risultati da una tabella indipendentemente dal fatto che corrispondano a un’altra. Questo è importante in quanto vogliamo includere tutti i risultati del cross join, che è tutte le combinazioni di genere e titolo, indipendentemente dal fatto che ci siano effettivamente dati riassunti.
Per riassumere, ecco i passaggi che stiamo prendendo per creare questo risultato:
- Ottieni un elenco distinto di JobTitles. Questo accade in un CTE.
- Ottieni un elenco distinto di generi. Questo accade in un CTE.,
- Creare tutte le possibili combinazioni di titoli di lavoro e generi utilizzando CROSS JOIN.
- Calcola un conteggio sommario dei dipendenti per titolo di lavoro e sesso.
- Abbina il conteggio del riepilogo calcolato con un elenco distinto.
Ecco la query finale che compie questi passaggi:
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;
Per rendere più facile la lettura, i due CTE e CROSS JOIN per creare tutte le combinazioni di JobTitle e Gender sono colorati in blu e rosso. Il CTE per riassumere i dati è di colore verde.
Si noti inoltre che usiamo la funzione SQL COALESCE per sostituire NULL con un valore zero.,
Ecco il risultato della query:
INNER JOIN as CROSS JOIN
Man mano che conosci SQL ti rendi conto che di solito c’è più di un modo per scrivere una query. Ad esempio, c’è un modo usando la clausola WHERE per far sì che un CROSS JOIN si comporti come un JOIN INTERNO.
Prendiamo queste due tabelle:
Supponiamo di voler interrogare tutti i dipendenti e mostrare la loro data di nascita e cognome. Per fare questo dobbiamo mettere in relazione la tabella dei dipendenti alla persona.,
Come abbiamo visto possiamo scrivere un cross join per combinare le righe come so
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person P
Ma questa query non è troppo utile in questo caso, in quanto restituisce 5.791.880 righe!
Per limitare le combinazioni di righe in modo che i record person siano correttamente abbinati alle righe dei dipendenti, possiamo usare una clausola WHERE. Ecco la query finale:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E CROSS JOIN Person.Person PWHERE P.BusinessEntityID = E.BusinessEntityID
Ecco le prime 14 righe di 290:
Questa query restituisce gli stessi risultati di una scritta con un JOIN INTERNO., La query che utilizza un join interno è:
SELECT P.LastName, E.BirthDateFROM HumanResources.Employee E INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
Quale query è migliore? Se guardi i piani di query, vedrai che sono molto simili.
Ecco il piano per il cross join
Ecco il piano per il join interno
Come vedi sono identici. Il motivo per cui sono così, è che SQL è un linguaggio dichiarativo, nel senso che diciamo al DB quale risultato vogliamo, non necessariamente come farlo. Quando forniamo il DBMS con la nostra query, l’ottimizzatore ha messo insieme il piano migliore., Nella maggior parte dei casi sarà lo stesso per le dichiarazioni equivalenti.