SQL - Cross Join Or Full Join Or Full Outer Join

If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, performs a full join. A full join is also called a cross join because each row of each table is crossed with each row in every other table to produce all possible combinations. This is also known as the Cartesian Product.

Joining tables this way has the potential to produce a very large number of rows because the possible row count is the product of the number of rows in each table. A full join between three tables that contain 10, 20, and 30 rows, respectively, could return 10x20x30 = 6 thousand rows.

The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SELECT DISTINCT JobTitle

          FROM   HumanResources.Employee

SELECT DISTINCT Gender

          FROM   HumanResources.Employee

SELECT JobTitle,

       Gender,

       COUNT(1) AS NumberEmployees

          FROM HumanResources.Employee

          GROUP BY JobTitle, Gender

WITH cteJobTitle (JobTitle)

AS  (SELECT DISTINCT JobTitle

     FROM   HumanResources.Employee),

cteGender (Gender)

AS (SELECT DISTINCT Gender

    FROM   HumanResources.Employee)

SELECT   J.JobTitle,

         G.Gender

FROM     cteJobTitle AS J

         CROSS JOIN cteGender AS G

ORDER BY J.JobTitle

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 NumberEmployees

FROM     cteJobTitle AS J

         CROSS JOIN cteGender AS G

         LEFT OUTER JOIN cteCounts AS C

         ON C.JobTitle = J.JobTitle

            AND C.Gender = G.Gender

ORDER BY J.JobTitle, G.Gender;