SQLite | Joining Data | Cross Join (CROSS JOIN)
A cross join returns every combination of rows from two tables. This article explains how to use the CROSS JOIN clause.
Cross Joins
A cross join returns every possible pairing of rows from two tables.
For each row in the left table, SQLite combines every row from the right table, repeating until all left-table rows have been processed.
If the left table has n rows and the right table has m rows, the result has n × m rows. This can produce a very large result.
Using CROSS JOIN
Combine SELECT with CROSS JOIN to query the Cartesian product of two tables. The syntax is as follows.
SELECT table_name.column_name, ... FROM table1
CROSS JOIN table2
ON table1.column1 = table2.column2;
A simplified form is shown below.
SELECT (result_columns) FROM table1
CROSS JOIN table2 ON (join_condition);
Although an ON clause may be specified, CROSS JOIN is normally used without a join condition, so the following examples omit it.
–
Create a product table and a color table, then insert sample data into both.
create table product (id integer, name text);
insert into product values (1, 'Desk');
insert into product values (2, 'Cabinet');
insert into product values (3, 'Chair');
sqlite> create table product (id integer, name text);
sqlite>
sqlite> insert into product values (1, 'Desk');
sqlite> insert into product values (2, 'Cabinet');
sqlite> insert into product values (3, 'Chair');
sqlite>
create table color (id integer, name text);
insert into color values (1, 'Red');
insert into color values (2, 'Blue');
insert into color values (3, 'White');
sqlite> create table color (id integer, name text);
sqlite>
sqlite> insert into color values (1, 'Red');
sqlite> insert into color values (2, 'Blue');
sqlite> insert into color values (3, 'White');
sqlite>
Run the following cross join.
select * from product cross join color;
sqlite> select * from product cross join color;
id name id name
---------- ---------- ---------- ----------
1 Desk 1 Red
1 Desk 2 Blue
1 Desk 3 White
2 Cabinet 1 Red
2 Cabinet 2 Blue
2 Cabinet 3 White
3 Chair 1 Red
3 Chair 2 Blue
3 Chair 3 White
sqlite>
The result pairs each row in product with every row in color.
Selecting Result Columns
Columns are selected as with an inner join. Qualify names shared by both tables as table_name.column_name; a name unique to one table may be used without the table prefix.
Apply this column selection to the cross join.
select product.id, product.name, color.name from product cross join color;
sqlite> select product.id, product.name, color.name from product cross join color;
id name name
---------- ---------- ----------
1 Desk Red
1 Desk Blue
1 Desk White
2 Cabinet Red
2 Cabinet Blue
2 Cabinet White
3 Chair Red
3 Chair Blue
3 Chair White
sqlite>