When should we use CROSS APPLY over INNER JOIN in MySQL

CROSS APPLY and INNER JOIN are used in MySQL to combine data from multiple tables. When dealing with row-wise operations or dynamic subqueries, CROSS APPLY is more efficient than INNER JOIN. CROSS APPLY allows for more detailed control on individual rows, while INNER JOIN operates on sets of data.

It becomes very useful when we need to perform row-wise operations or apply subqueries dynamically for each row. Therefore, when dealing with such tasks, CROSS APPLY is preferred over INNER JOIN in MySQL.

Understanding INNER JOIN

INNER JOIN in MySQL is used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match found in both tables.

The syntax for INNER JOIN in MySQL is as follows:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Exploring CROSS APPLY

CROSS APPLY, is a specific feature in SQL Server and not directly available in MySQL. However, similar functionality can be achieved using other methods like INNER JOIN combined with subqueries or derived tables.

The syntax for CROSS JOIN in MySQL is as follows:

SELECT column_list
FROM outer_table
CROSS APPLY (
    -- Subquery or table-valued function
    inner_table_expression
) AS alias;

Setting up Environment

To understand CROSS APPLY and INNER JOIN in MySQL, we will create 2 tables write queries, and understand the output one by one.

Query:

-- Creating the 'students' table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course_id INT
);

-- Creating the 'courses' table
CREATE TABLE courses (-- Inserting data into the 'students' table
INSERT INTO students (student_id, name, age, course_id)
VALUES (1, 'John Smith', 18, 101);

INSERT INTO students (student_id, name, age, course_id)
VALUES (2, 'Alice Johnson', 17, 102);

INSERT INTO students (student_id, name, age, course_id)
VALUES (3, 'Michael Brown', 19, 101);

-- Inserting data into the 'courses' table
INSERT INTO courses (course_id, course_name)
VALUES (101, 'Mathematics');

INSERT INTO courses (course_id, course_name)
VALUES (102, 'Science');

    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

Example of When should we use CROSS APPLY over INNER JOIN in MySQL

Example 1: INNER JOIN to Retrieve Students with their Course Names

In this example, we will combine the data from the students and courses table using INNER JOIN.

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.course_id = courses.course_id;

Output:

Example 1: INNER JOIN

Explanation: This query uses an INNER JOIN to combine data from the students and courses tables based on their course_id. It retrieves the names of students along with the names of the courses they are enrolled in.

Example 2: INNER JOIN with Conditions

Here, in this example again we will use INNERJOIN between students and courses with a filter to get the name of students related to a particular course in the courses table.

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.course_id = courses.course_id
WHERE courses.course_name = 'Mathematics';

Output:

Example 2: INNER JOIN

Explanation: Here, we apply an INNER JOIN between students and courses, but we add a condition to filter results. This query retrieves the names of students enrolled in the ‘Mathematics‘ course only.

Example 3: CROSS APPLY to Retrieve all Students with their Courses

In this example, we will use CROSS APPLY to get the students with courses they are enrolled in.

SELECT students.name, courses.course_name
FROM students
CROSS APPLY (
    SELECT course_name
    FROM courses
    WHERE students.course_id = courses.course_id
) AS course_info;

Output:

Example 3: CROSS APPLY

Explanation: This query employs a CROSS APPLY operation, allowing us to correlate the students and course tables. It retrieves all students along with their corresponding course names.

Example 4: CROSS APPLY with Conditions

Here in this example, we will filter the results obtained from CROSS APPLY with conditions.

SELECT students.name, courses.course_name
FROM students
CROSS APPLY (
    SELECT course_name
    FROM courses
    WHERE students.course_id = courses.course_id
      AND courses.course_name = 'Mathematics'
) AS course_info;

Output:

Example 4: CROSS APPLY

Explanation: Here, we use CROSS APPLY with conditions to filter results. This query retrieves the names of students along with their corresponding course names, but only for the ‘Mathematics‘ course.

Conclusion

The use of CROSS APPLY in MySQL becomes very useful when we need to perform row-wise operations or apply subqueries dynamically for each row while INNER JOIN operates on sets of data. The CROSS APPLY helps us for more detailed control of individual rows. Therefore, when we deal with tasks that involve row-wise operations or dynamic subqueries then CROSS APPLY over INNER JOIN in MYSQL is more efficient.