MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB - CROSS JOIN



The MariaDB 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.

MariaDB CROSS JOIN

Syntax

The syntax for using CROSS JOIN keyword in MariaDB 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-80XXXXX0002XXX, Brooklyn, New York, USAF
+33-14XXXXX013XXX, Grenelle, Paris, FranceM
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, NetherlandsF
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, ChinaM
+65-67XXXXX47XXX, Yishun, SingaporeM
+81-35XXXXX728XXX, Koto 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
    John25XXX, Brooklyn, New York, USA
    John25XXX, Grenelle, Paris, France
    John25XXX, Geuzenveld, Amsterdam, Netherlands
    John25XXX, Yizhuangzhen, Beijing, China
    John25XXX, Yishun, Singapore
    John25XXX, Koto City, Tokyo, Japan
    Marry24XXX, Brooklyn, New York, USA
    Marry24XXX, Grenelle, Paris, France
    Marry24XXX, Geuzenveld, Amsterdam, Netherlands
    Marry24XXX, Yizhuangzhen, Beijing, China
    Marry24XXX, Yishun, Singapore
    Marry24XXX, Koto City, Tokyo, Japan
    Jo27XXX, Brooklyn, New York, USA
    Jo27XXX, Grenelle, Paris, France
    Jo27XXX, Geuzenveld, Amsterdam, Netherlands
    Jo27XXX, Yizhuangzhen, Beijing, China
    Jo27XXX, Yishun, Singapore
    Jo27XXX, Koto City, Tokyo, Japan
    Kim30XXX, Brooklyn, New York, USA
    Kim30XXX, Grenelle, Paris, France
    Kim30XXX, Geuzenveld, Amsterdam, Netherlands
    Kim30XXX, Yizhuangzhen, Beijing, China
    Kim30XXX, Yishun, Singapore
    Kim30XXX, Koto City, Tokyo, Japan
    Ramesh28XXX, Brooklyn, New York, USA
    Ramesh28XXX, Grenelle, Paris, France
    Ramesh28XXX, Geuzenveld, Amsterdam, Netherlands
    Ramesh28XXX, Yizhuangzhen, Beijing, China
    Ramesh28XXX, Yishun, Singapore
    Ramesh28XXX, Koto City, Tokyo, Japan
    Huang28XXX, Brooklyn, New York, USA
    Huang28XXX, Grenelle, Paris, France
    Huang28XXX, Geuzenveld, Amsterdam, Netherlands
    Huang28XXX, Yizhuangzhen, Beijing, China
    Huang28XXX, Yishun, Singapore
    Huang28XXX, 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 MariaDB 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
    Marry24XXX, Brooklyn, New York, USA
    Jo27XXX, Grenelle, Paris, France
    Kim30XXX, Geuzenveld, Amsterdam, Netherlands
    Huang28XXX, Yizhuangzhen, Beijing, China

5