MySQL CROSS JOIN

Introduction

In this chapter, we will learn about the CROSS JOIN in MySQL. A CROSS JOIN returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables. This can result in a large number of rows, especially if both tables have many rows. We will cover the syntax, a simple example, and important considerations for using the CROSS JOIN.

Syntax

The basic syntax for the CROSS JOIN is:

SELECT columns
FROM table1
CROSS JOIN table2;

• columns: The columns you want to retrieve.
• table1 and table2: The tables to join.

Example with CROSS JOIN

Let’s go through a simple example where we use CROSS JOIN to retrieve data from the students and courses tables.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create Tables:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

4. Insert Data into the Tables:

INSERT INTO students (first_name, last_name) VALUES
('Rahul', 'Sharma'),
('Priya', 'Singh');

INSERT INTO courses (course_name) VALUES
('Mathematics'),
('Science');

5. Use the CROSS JOIN:

SELECT students.first_name, students.last_name, courses.course_name
FROM students
CROSS JOIN courses;

Output

Explanation

In the above example:

• The CROSS JOIN is used to combine rows from the students and courses tables.
• The result set includes all possible combinations of rows from both tables.
• Since there are 2 students and 2 courses, the result set contains 2 * 2 = 4 rows.

Important Considerations

• Result Set Size: The CROSS JOIN returns a Cartesian product, which can result in a large number of rows. Be cautious when using CROSS JOIN with large tables.
• Use Cases: CROSS JOIN is useful when you need to generate combinations of rows from two tables, such as creating schedules, testing scenarios, or generating combinations for analysis.
• Performance: Due to the potentially large result set, CROSS JOIN can impact performance. Ensure that the use of CROSS JOIN is appropriate for your specific use case.

Conclusion

The CROSS JOIN is used for generating the Cartesian product of two tables in MySQL. This chapter covered the syntax and usage of the CROSS JOIN, provided a simple example with the students and courses tables, and discussed important considerations. In the next chapter, we will explore the SELF JOIN and its use cases.