MySQL CROSS JOIN

MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. Among these operations, MySQL also provides the CROSS JOIN statement to combine rows from different tables.

In this article, we will learn about the concept of CROSS JOIN in MySQL, exploring its applications through examples of both simple and complex CROSS JOIN operations.

MySQL CROSS JOIN

MySQL CROSS JOIN is a join procedure that is used to combine two or more tables. It is also known as a Cartesian join and returns the Cartesian product of two or more tables. It combines each row of one table with each row of another table and returns a new table with all possible combinations.

Syntax:

SELECT * FROM table1

CROSS JOIN table2;

In this syntax,

• Table 1 and Table 2 are the names of the tables.
• The asterisk (*) is a wildcard character representing all tables’ columns.

Examples of CROSS JOIN

Example 1: Simple CROSS JOIN

In this example, there a two tables of customers and orders, including the customer_id as a foreign key. The CROSS JOIN combines every row from the customers table with every row from the orders table.

First, we will create customers and orders tables by using the following query:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Then, we will add some records in both of these table by using the following query:

-- Insert data into the customers table
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');
INSERT INTO customers VALUES (3, 'Customer C');

-- Insert data into the orders table
INSERT INTO orders VALUES (101, '2024-01-01', 1);
INSERT INTO orders VALUES (102, '2024-01-02', 2);
INSERT INTO orders VALUES (103, '2024-01-03', 3);

Now, we will use the following query to perform the CROSS JOIN operation:

SELECT * FROM customers
CROSS JOIN orders;

Output:

simple-cross-join

Example 2: Complex CROSS JOIN

In this example, there a three tables students, courses, and grades. The CROSS JOIN combines every row from the three tables and returns all possible combinations of a student, course, and grade.

First, we will create students, courses,the and grades tables by using the following query:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE grades (
    grade_id INT PRIMARY KEY,
    grade_name VARCHAR(2)
);

Then, we will add some records in all of this table by using the following query:

-- Insert data into the students table
INSERT INTO students VALUES (1, 'Student A');
INSERT INTO students VALUES (2, 'Student B');

-- Insert data into the courses table
INSERT INTO courses VALUES (101, 'Math');
INSERT INTO courses VALUES (102, 'Science');

-- Insert data into the grades table
INSERT INTO grades VALUES (1, 'A');
INSERT INTO grades VALUES (2, 'B');

Now, we will use the following query to perform the CROSS JOIN operation:

SELECT * FROM students
CROSS JOIN courses
CROSS JOIN grades;
represents

Output:

complex-cross-join

Conclusion

MySQL CROSS JOIN statement helps us to get a Cartesian product of two or more tables. It combines each row of one table with each row of another table and returns a new table with all possible combinations. However, it should be carefully used because it can lead to large result sets and potential performance issues. Users should apply CROSS JOIN selectively, taking into account the specific requirements of their queries and the potential impact on performance.