When should we use CROSS APPLY over INNER JOIN in MySQL
Last Updated :
09 Apr, 2024
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 APPLYExplanation: 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 APPLYExplanation: 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.
Similar Reads
When Should We Use CROSS APPLY Over INNER JOIN? In SQL Server, both INNER JOIN and CROSS APPLY are used to combine data from multiple tables, but they serve different purposes and have distinct use cases. INNER JOIN is typically used to match rows between two tables based on a related column, returning only the rows where a match exists in both t
4 min read
CROSS APPLY vs INNER JOIN in PL/SQL PL/SQL Stands for procedural language extension to SQL. In a procedure, the role of the subprogram is to perform a particular task and it is a unit module of a program. It combined to form larger programs. A subprogram can be involved by another program which is called the calling program. PL/SQL pr
6 min read
INNER JOIN ON vs WHERE clause in MySQL When working with MySQL queries that involve multiple tables, understanding how to effectively use INNER JOIN ON versus the WHERE clause can significantly impact query performance and clarity. These two SQL constructs serve distinct purposes in combining data from different tables based on specific
5 min read
INNER JOIN ON vs WHERE clause in SQL Server In SQL Server, joining tables and filtering data are essential for retrieving meaningful information. The INNER JOIN operation is used to combine rows from multiple tables based on a matching condition, while the WHERE clause allows for further filtering of results. In this article, we will PL/SQL S
7 min read
How to Improve Performance of a MySQL Query with Complicated WHERE Logic? Optimizing MySQL query performance is important for maintaining efficient and speedy database applications, especially with complex WHERE logic. As databases grow, poorly optimized queries can cause significant performance issues.This article provides techniques to enhance MySQL query performance, i
3 min read