MySQL - Cross Join

Table of content

MySQL Cross Join
Joining Multiple Tables with Cross Join
Cross Join Using Client Program

MySQL Cross Join

A MySQL Cross Join combines each row of the first table with each row of second table. It is a basic type of inner join that is used to retrieve the Cartesian product (or cross product) of two individual tables (i.e. permutations).

A Cartesian product, or a cross product, is the result achieved from multiplication of two sets. This is done by multiplying all the possible pairs from both the sets.

The sample figure below illustrates the cross join in a simple manner.

Syntax

Following is the basic syntax of the Cross Join query in MySQL −

SELECT column_name(s)
FROM table1
CROSS JOIN table2

Example

In this example of cross join, let us first create a table named CUSTOMERS, which contains personal details of customers including their name, age, address and salary etc.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 );

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES 
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00);

The table is displayed as follows −

OID	DATE	CUSTOMER_ID	AMOUNT
100	2009-10-08 00:00:00	3	1500.00
101	2009-11-20 00:00:00	2	1560.00

Now, if we execute the following Cross Join query on these two tables given above, the cross join combines each row in CUSTOMERS table with each row in ORDERS table.

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
CROSS JOIN ORDERS;

Output

The resultant table is as follows −

ID	NAME	AMOUNT	DATE
2	Khilan	1500.00	2009-10-08 00:00:00
1	Ramesh	1560	2009-11-20 00:00:00
2	Khilan	1560	2009-11-20 00:00:00
1	Ramesh	1500.00	2009-10-08 00:00:00

Joining Multiple Tables with Cross Join

We can also join more than two tables using cross join. In this case, multiple-way permutations are displayed and the resultant table is expected to contain way more records than the individual tables.

Syntax

Following is the syntax to join multiple tables using cross join in MySQL −

SELECT column_name(s)
FROM table1
CROSS JOIN table2
CROSS JOIN table3
CROSS JOIN table4
.
.
.

Example

Let us now combine three tables CUSTOMERS, ORDERS and ORDER_RANGE, to demonstrate combining multiple tables using cross join.

We will create the ORDER_RANGE table using the query below −

CREATE TABLE ORDER_RANGE (
   SNO INT NOT NULL,
   ORDER_RANGE VARCHAR (20) NOT NULL,
);

Now, we can insert values into this empty tables using the INSERT statement as follows −

INSERT INTO ORDER_RANGE VALUES
(1, '1-100'),
(2, '100-200'),
(3, '200-300');

The ORDER_RANGE table is as follows −

SNO	ORDER_RANGE
1	1-100
2	100-200
3	200-300

Now we use the following cross join query on the given tables,

SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE
FROM CUSTOMERS
CROSS JOIN ORDERS
CROSS JOIN ORDER_RANGE;

Output

The resultant table is given below −

ID	NAME	AMOUNT	DATE	ORDER_RANGE
2	Khilan	1560	2009-11-20 00:00:00	1-100
1	Ramesh	1560	2009-11-20 00:00:00	1-100
2	Khilan	1500.00	2009-10-08 00:00:00	1-100
1	Ramesh	1500.00	2009-10-08 00:00:00	1-100
2	Khilan	1560	2009-11-20 00:00:00	100-200
1	Ramesh	1560	2009-11-20 00:00:00	100-200
2	Khilan	1500.00	2009-10-08 00:00:00	100-200
1	Ramesh	1500.00	2009-10-08 00:00:00	100-200
2	Khilan	1560	2009-11-20 00:00:00	200-300
1	Ramesh	1560	2009-11-20 00:00:00	200-300
2	Khilan	1500.00	2009-10-08 00:00:00	200-300
1	Ramesh	1500.00	2009-10-08 00:00:00	200-300

Cross Join Using Client Program

We can also perform the Cross join operation on one or more tables using a client program.

Syntax

PHP NodeJS Java Python

To perform cross Join through a PHP program, we need to execute the SQL query with CROSS JOIN clause using the mysqli function query() as follows −

$sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
$mysqli->query($sql);

To perform cross Join through a JavaScript program, we need to execute the SQL query with CROSS JOIN clause using the query() function of mysql2 library as follows −

sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
con.query(sql);

To perform cross Join through a Java program, we need to execute the SQL query with CROSS JOIN clause using the JDBC function executeQuery() as follows −

String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b 
ON a.tutorial_author = b.tutorial_author";
statement.executeQuery(sql);

To perform cross Join through a Python program, we need to execute the SQL query with CROSS JOIN clause using the execute() function of the MySQL Connector/Python as follows −

cross_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS"
cursorObj.execute(cross_join_query)

Example

Following are the programs −

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    FROM tutorials_tbl a CROSS JOIN tcount_tbl b';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the details after executing cross join! \n";
    while ($row = $result->fetch_assoc()) {
        printf("Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"]);
        printf("\n");
    }
} else {
    printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

following is the details after executing cross join!
Id: 102, Author: Sarika Singh, Count: 20
Id: 101, Author: Aman kumar, Count: 20
Id: 3, Author: Sanjay, Count: 20
Id: 2, Author: Abdul S, Count: 20
Id: 1, Author: John Poul, Count: 20
Id: 102, Author: Sarika Singh, Count: 5
Id: 101, Author: Aman kumar, Count: 5
Id: 3, Author: Sanjay, Count: 5
Id: 2, Author: Abdul S, Count: 5
Id: 1, Author: John Poul, Count: 5
Id: 102, Author: Sarika Singh, Count: 4
Id: 101, Author: Aman kumar, Count: 4
Id: 3, Author: Sanjay, Count: 4
Id: 2, Author: Abdul S, Count: 4
Id: 1, Author: John Poul, Count: 4
Id: 102, Author: Sarika Singh, Count: 20
Id: 101, Author: Aman kumar, Count: 20
Id: 3, Author: Sanjay, Count: 20
Id: 2, Author: Abdul S, Count: 20
Id: 1, Author: John Poul, Count: 20
Id: 102, Author: Sarika Singh, Count: 1
Id: 101, Author: Aman kumar, Count: 1
Id: 3, Author: Sanjay, Count: 1
Id: 2, Author: Abdul S, Count: 1
Id: 1, Author: John Poul, Count: 1
Id: 102, Author: Sarika Singh, Count: 1
Id: 101, Author: Aman kumar, Count: 1
Id: 3, Author: Sanjay, Count: 1
Id: 2, Author: Abdul S, Count: 1
Id: 1, Author: John Poul, Count: 1

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //Cross Join
  sql =
    "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 20
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 20
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 20 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 20 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 5
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 5
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 5 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 5 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 4
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 4
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 4 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 4 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 20
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 20
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 20 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 20 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 1
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 1
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 1 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 1 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 1
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 1
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 1 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 1 },
  {
    tutorial_id: 102,
    tutorial_author: 'Sarika Singh',
    tutorial_count: 2
  },
  {
    tutorial_id: 101,
    tutorial_author: 'Aman kumar',
    tutorial_count: 2
  },
  { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: 2 },
  { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CrossJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //MySQL CROSS JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after CROSS Join...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Table records after CROSS Join...!
1 John Paul 1
3 Sanjay 1

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
cross_join_query = f"""SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS"""
cursorObj.execute(cross_join_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

(1, 'Ramesh', 2060, '2008-05-20 00:00:00')
(1, 'Ramesh', 1560, '2009-11-20 00:00:00')
(1, 'Ramesh', 1500, '2009-10-08 00:00:00')
(1, 'Ramesh', 3000, '2009-10-08 00:00:00')
(2, 'Khilan', 2060, '2008-05-20 00:00:00')
(2, 'Khilan', 1560, '2009-11-20 00:00:00')
(2, 'Khilan', 1500, '2009-10-08 00:00:00')
(2, 'Khilan', 3000, '2009-10-08 00:00:00')
(3, 'kaushik', 2060, '2008-05-20 00:00:00')
(3, 'kaushik', 1560, '2009-11-20 00:00:00')
(3, 'kaushik', 1500, '2009-10-08 00:00:00')
(3, 'kaushik', 3000, '2009-10-08 00:00:00')
(4, 'Chaital', 2060, '2008-05-20 00:00:00')
(4, 'Chaital', 1560, '2009-11-20 00:00:00')
(4, 'Chaital', 1500, '2009-10-08 00:00:00')
(4, 'Chaital', 3000, '2009-10-08 00:00:00')
(5, 'Hardik', 2060, '2008-05-20 00:00:00')
(5, 'Hardik', 1560, '2009-11-20 00:00:00')
(5, 'Hardik', 1500, '2009-10-08 00:00:00')
(5, 'Hardik', 3000, '2009-10-08 00:00:00')
(6, 'Komal', 2060, '2008-05-20 00:00:00')
(6, 'Komal', 1560, '2009-11-20 00:00:00')
(6, 'Komal', 1500, '2009-10-08 00:00:00')
(6, 'Komal', 3000, '2009-10-08 00:00:00')
(7, 'Muffy', 2060, '2008-05-20 00:00:00')
(7, 'Muffy', 1560, '2009-11-20 00:00:00')
(7, 'Muffy', 1500, '2009-10-08 00:00:00')
(7, 'Muffy', 3000, '2009-10-08 00:00:00')