SQLite - CROSS JOIN

The SQLite CROSS JOIN keyword is used to returns all records from both tables (table1 and table2). It is sometimes called CARTESIAN JOIN because in the absence of a WHERE condition it behaves like a CARTESIAN PRODUCT i.e., the number of rows in the result-set is the product of the number of rows of the two tables.

Syntax

The syntax for using CROSS JOIN keyword in SQLite is given below:

SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
CROSS JOIN table2;

Example:

Consider database tables called Employee and Contact_Info with the following records:

Table 1: Employee table

Table 2: Contact_Info table

• To CROSS JOIN Employee and Contact_Info tables, considering Name and Age columns from Employee table and Address column from Contact_Info table, the following query can be used:

  SELECT Employee.Name, Employee.Age, Contact_Info.Address 
  FROM Employee
  CROSS JOIN Contact_Info;
  

  
  

  This will produce the result as shown below (Note that the produced result contains each possible combination of record-set from both tables):

  Name	Age	Address
  John	25	Brooklyn, New York, USA
  John	25	Grenelle, Paris, France
  John	25	Geuzenveld, Amsterdam, Netherlands
  John	25	Yizhuangzhen, Beijing, China
  John	25	Yishun, Singapore
  John	25	Koto City, Tokyo, Japan
  Marry	24	Brooklyn, New York, USA
  Marry	24	Grenelle, Paris, France
  Marry	24	Geuzenveld, Amsterdam, Netherlands
  Marry	24	Yizhuangzhen, Beijing, China
  Marry	24	Yishun, Singapore
  Marry	24	Koto City, Tokyo, Japan
  Jo	27	Brooklyn, New York, USA
  Jo	27	Grenelle, Paris, France
  Jo	27	Geuzenveld, Amsterdam, Netherlands
  Jo	27	Yizhuangzhen, Beijing, China
  Jo	27	Yishun, Singapore
  Jo	27	Koto City, Tokyo, Japan
  Kim	30	Brooklyn, New York, USA
  Kim	30	Grenelle, Paris, France
  Kim	30	Geuzenveld, Amsterdam, Netherlands
  Kim	30	Yizhuangzhen, Beijing, China
  Kim	30	Yishun, Singapore
  Kim	30	Koto City, Tokyo, Japan
  Ramesh	28	Brooklyn, New York, USA
  Ramesh	28	Grenelle, Paris, France
  Ramesh	28	Geuzenveld, Amsterdam, Netherlands
  Ramesh	28	Yizhuangzhen, Beijing, China
  Ramesh	28	Yishun, Singapore
  Ramesh	28	Koto City, Tokyo, Japan
  Huang	28	Brooklyn, New York, USA
  Huang	28	Grenelle, Paris, France
  Huang	28	Geuzenveld, Amsterdam, Netherlands
  Huang	28	Yizhuangzhen, Beijing, China
  Huang	28	Yishun, Singapore
  Huang	28	Koto City, Tokyo, Japan

Using WHERE / ON Clause with CROSS JOIN

Adding a WHERE / ON clause (specifying relationship between both tables), the CROSS JOIN will produce the same result as the INNER JOIN clause.

Syntax

The syntax for using CROSS JOIN with WHERE / ON clause in SQLite is given below:

/* Using ON clause */
SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
CROSS JOIN table2
ON table1.matching_column = table2.matching_column;

/* Using WHERE clause */
SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
CROSS JOIN table2
WHERE table1.matching_column = table2.matching_column;

Example:

Consider above discussed database tables Employee and Contact_Info.

• To CROSS JOIN Employee and Contact_Info tables based on matching column EmpID, the following query can be used:

  SELECT Employee.Name, Employee.Age, Contact_Info.Address 
  FROM Employee
  CROSS JOIN Contact_Info
  ON Employee.EmpID = Contact_Info.EmpID;
  

  
  

  This will produce the result as shown below:

  Name	Age	Address
  Marry	24	Brooklyn, New York, USA
  Jo	27	Grenelle, Paris, France
  Kim	30	Geuzenveld, Amsterdam, Netherlands
  Huang	28	Yizhuangzhen, Beijing, China