Does JOIN Order Matter in SQL?

1. Introduction

JOIN operation is one of the most common operations in SQL. It is used for managing relational databases by combining rows from two or more tables based on a related column between them.

In this tutorial, we’ll explain how the order of joining affects the outcome for different types of JOIN statements.

2. Does the Order of JOINs Matter?

There are multiple types of JOIN, such as INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

For INNER JOIN, the order doesn’t affect the final results. On the other hand, the order of OUTER JOINs is more complicated based on different scenarios. We’ll explore the difference between JOIN statements using the database from Baeldung University Schema.

3. INNER JOIN

When joining two or multiple tables, the order of INNER JOINs does not affect the final result set. This is because INNER JOIN is commutative, meaning the outcome remains the same regardless of A INNER JOIN B or B INNER JOIN A.

This rule also applies when joining multiple tables. For example, let’s try to extract teaching role, semester, year, name, course name, course credits, and faculty name from our database. This information can be found in three different tables: Teaching, Course, and Faculty. Therefore, we can join these tables with the  faculty_id and course_id using the following queries:

SELECT
    t.role, t.semester, t.year, f.name, c.name, c.credits
FROM
    Teaching t
INNER JOIN
    Faculty f ON t.faculty_id = f.id
INNER JOIN
    Course c ON t.course_id = c.id;

SELECT
    t.role, t.semester, t.year, f.name, c.name, c.credits
FROM
    Course c
INNER JOIN
    Teaching t ON c.id = t.course_id 
INNER JOIN
    Faculty f ON t.faculty_id = f.id;

Although we join the tables in various orders, these two queries will return the same results as long as we use SELECT instead of SELECT *.

4. OUTER JOIN

When using OUTER JOIN, the order of the table becomes more critical. Changing the order might result in different outputs.

OUTER JOINs contain LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN. In this tutorial, we’ll take LEFT JOIN as an example.

4.1. JOIN Two Tables

In LEFT JOIN, the left table is dominant, meaning all its rows are included in the result set. The right table contributes rows only where there is a match. Therefore, A LEFT JOIN B will include all rows from A, while B LEFT JOIN A will include all rows from B. In the university database, we can extract department names and their corresponding faculties as follows:

SELECT 
    d.name, f.name
FROM
    Department d
LEFT JOIN
    Faculty f ON d.id = f.department_id;

This query will return all departments, including those with no faculty members associated with them (i.e. f.name will be NULL).

However, if we change the order of the JOIN operation, the output will be changed accordingly:

SELECT 
    f.name, d.name
FROM
    Faculty f
LEFT JOIN
    Department d ON f.department_id = d.id;

In this case, the query will return all the faculty members. For the faculty members without a department, d.name will be NULL.

4.2. JOIN Multiple Tables

Similar to INNER JOIN, changing the order of LEFT JOIN statements will have the same results. For instance, A LEFT JOIN B LEFT JOIN C is equivalent to A LEFT JOIN C LEFT JOIN B.

However, in some more complicated cases, things might change. For example:

SELECT 
    t.id, c.id, f.id
FROM
    Teaching t 
LEFT JOIN
    Course c ON t.course_id = c.id
LEFT JOIN
    Faculty f ON t.faculty_id = f.id AND c.department_id = f.department_id;

is not equivalent to:

SELECT 
    t.id, c.id, f.id
FROM
    Teaching t 
LEFT JOIN
    Faculty f ON t.course_id = f.id
LEFT JOIN
    Course c ON t.course_id = c.id AND c.department_id = f.department_id;

In the first query, we first join Teaching with Course if the id matches. Then we join Teaching with Faculty. However, with the extra AND condition, if the course and the faculty don’t belong to the same department, even if the faculty id matches between table Teaching and Faculty, f.id will be NULL.

Meanwhile, the second query will have all the faculty information if the id matches the id in the Teaching table. However, some course ids might become NULL because the course and the faculty might not be in the same department.

5. Conclusion

In this tutorial, we investigated the effect of changing the JOIN statement order. Changing the order of INNER JOIN doesn’t affect the result of a query. By contrast, different orders of OUTER JOIN might result in different outputs. Thus, we need to be more cautious when performing an OUTER JOIN.