SQL CROSS JOINs

I found a use for CROSS JOIN in SQL recently when generating content for a pivot table.

Given a fixtures table:

CREATE SEQUENCE IF NOT EXISTS fixtures_id_seq; CREATE TABLE "public"."fixtures" ( "id" int4 NOT NULL DEFAULT nextval('fixtures_id_seq'::regclass), "start_time" TIMESTAMPTZ, "sport" VARCHAR(40), PRIMARY KEY ("id") ); 

The SQL is something similar to the below:

WITH sports AS ( SELECT DISTINCT sport FROM fixtures ORDER BY sport ), days AS ( SELECT day FROM generate_series( date_trunc('day', '2020-09-09'::date)::date, date_trunc('day', '2020-09-21'::date)::date, '1 day'::interval ) AS day ), fixtures_per_day AS ( SELECT date_trunc('day', start_time) AS day, sport, COUNT(*) AS c FROM fixtures GROUP BY date_trunc('day', start_time), sport ) SELECT days.day, sports.sport, COALESCE(fixtures_per_day.c, 0) AS fixture_count FROM days CROSS JOIN sports INNER JOIN fixtures_per_day ON ( days.day = fixtures_per_day.day AND sports.sport = fixtures_per_day.sport ) 

There are three CTEs (Common Table Expressions) which are like sub-queries but can be re-used. I’ve used them here to keep the query tidy.

The first “table”, sports just gathers us a list of each of the sports in a fixtures table. Next, days generates a sequence of dates from 9/Sep/2020 to 21/Sep/2020. Finally fixtures_per_day create a table with the count of events on any given day for any given sport.

Our query then CROSS JOINs the days and sports “tables” to create a cartesian product, a maths-y name for the result of the following Python code:

result = [] for day in days: for sport in sports: result.append((day, sport)) 

Once we have the CROSS JOIN we can LEFT JOIN on the fixtures_per_day to get something which would make sense in a pivot table or chart without having to fill in blanks by hand. We use the COALESCE function to make sure that any NULL rows become 0.

The results for some test data look like the following: