SQLite Tutorial SQLite Advanced SQLite Database SQLite References

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.

SQLite CROSS JOIN

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;

Note: The CROSS JOIN can potentially return very large result-sets.

Example:

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

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

  • 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):

    NameAgeAddress
    John25Brooklyn, New York, USA
    John25Grenelle, Paris, France
    John25Geuzenveld, Amsterdam, Netherlands
    John25Yizhuangzhen, Beijing, China
    John25Yishun, Singapore
    John25Koto City, Tokyo, Japan
    Marry24Brooklyn, New York, USA
    Marry24Grenelle, Paris, France
    Marry24Geuzenveld, Amsterdam, Netherlands
    Marry24Yizhuangzhen, Beijing, China
    Marry24Yishun, Singapore
    Marry24Koto City, Tokyo, Japan
    Jo27Brooklyn, New York, USA
    Jo27Grenelle, Paris, France
    Jo27Geuzenveld, Amsterdam, Netherlands
    Jo27Yizhuangzhen, Beijing, China
    Jo27Yishun, Singapore
    Jo27Koto City, Tokyo, Japan
    Kim30Brooklyn, New York, USA
    Kim30Grenelle, Paris, France
    Kim30Geuzenveld, Amsterdam, Netherlands
    Kim30Yizhuangzhen, Beijing, China
    Kim30Yishun, Singapore
    Kim30Koto City, Tokyo, Japan
    Ramesh28Brooklyn, New York, USA
    Ramesh28Grenelle, Paris, France
    Ramesh28Geuzenveld, Amsterdam, Netherlands
    Ramesh28Yizhuangzhen, Beijing, China
    Ramesh28Yishun, Singapore
    Ramesh28Koto City, Tokyo, Japan
    Huang28Brooklyn, New York, USA
    Huang28Grenelle, Paris, France
    Huang28Geuzenveld, Amsterdam, Netherlands
    Huang28Yizhuangzhen, Beijing, China
    Huang28Yishun, Singapore
    Huang28Koto 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:

    NameAgeAddress
    Marry24Brooklyn, New York, USA
    Jo27Grenelle, Paris, France
    Kim30Geuzenveld, Amsterdam, Netherlands
    Huang28Yizhuangzhen, Beijing, China