JOIN Syntax vs WHERE JOIN in SQL

1. Introduction

Combining data from several tables is a typical process when working with relational databases. Joins, which enable us to create significant relationships between tables, are used to achieve this.

In SQL, joins may be written primarily in two ways: utilizing the WHERE clause and the JOIN syntax.

In this tutorial, we’ll explore the differences between these two methods. We’ll examine their syntax, benefits, and drawbacks as well as provide examples to show when and why we would choose to utilize one over the other.

2. Overview of SQL Joins

Joins are a fundamental concept in SQL that allows us to combine rows from two or more tables based on a related column between them. This enables us to retrieve a comprehensive set of data from multiple tables in a single query.

A join in SQL is an operation that links rows from two or more tables based on a related column between them. The result is a new table that combines columns from the joined tables, providing a way to query related data together.

There are several types of joins in SQL, each serving a specific purpose depending on how we want to combine the data. The most common types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN and CROSS JOIN.

Generally, joins allow us to work with normalized databases where data is spread across multiple tables. Moreover, they enable the construction of complex queries that can fetch and combine related data.

Also, proper use of joins can lead to more efficient queries by allowing the database engine to optimize the retrieval process.

3. JOIN Syntax

The JOIN syntax is generally more readable and maintainable compared to using the WHERE clause for joining tables, especially as queries become more complex.

Let’s explore the JOIN syntax using a sample database schema for a university.

3.1. INNER JOIN

We’ll use an INNER JOIN to find all courses and their corresponding department names:

SELECT Course.name AS course_name, Department.name AS department_name
FROM Course
INNER JOIN Department ON Course.department_id = Department.id;

This query joins the Course table with the Department table based on the department_id column in the Course table matching the id column in the Department table.

3.2. LEFT JOIN

Next, we’ll use a LEFT JOIN to find all students and their corresponding department names, including students who are not enrolled in any department:

SELECT Student.name AS student_name, Department.name AS department_name
FROM Student
LEFT JOIN Department ON Student.id = Department.id;

3.3. CROSS JOIN

To illustrate a CROSS JOIN, which returns the Cartesian product of the two tables:

SELECT Student.name AS student_name, Course.name AS course_name
FROM Student
CROSS JOIN Course;

This query returns all possible combinations of students and courses, which can be useful in certain analytical scenarios but often produces a large number of rows.

3.4. FULL OUTER JOIN

This query returns all students and their departments, including unmatched rows from both tables:

SELECT Student.name AS student_name, Department.name AS department_name
FROM Student
LEFT JOIN Department ON Student.id = Department.id
UNION
SELECT Student.name, Department.name
FROM Student
RIGHT JOIN Department ON Student.id = Department.id;

4. WHERE JOIN

When merging data from several tables, we may also utilize the WHERE clause in addition to the JOIN syntax to get comparable outcomes. This technique is frequently found in simpler queries or older SQL code. However, when queries get more complicated, they might become harder to maintain and less understandable.

Let’s examine the utilization of the university schema with the WHERE join syntax. The examples from the preceding section will be rewritten with the WHERE clause in place of the JOIN syntax.

4.1. INNER JOIN

To find all courses and their corresponding department names by using the WHERE clause:

SELECT Course.name AS course_name, Department.name AS department_name
FROM Course, Department
WHERE Course.department_id = Department.id;

In this query, we list both tables in the FROM clause and specify the join condition in the WHERE clause. This achieves the same result as the INNER JOIN example in the previous section.

4.2. LEFT JOIN

It is not straightforward to use the WHERE clause to achieve a similar result to a LEFT JOIN. However, we can create a scenario in which students are matched with departments based on hypothetical relationships.

Suppose we had an Enrollment table that records which students are enrolled in which courses:

CREATE TABLE Enrollment
(
    student_id INT,
    course_id VARCHAR(10),
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT enrollment_student_id_fkey FOREIGN KEY(student_id) REFERENCES Student(id),
    CONSTRAINT enrollment_course_id_fkey FOREIGN KEY(course_id) REFERENCES Course(id)
);

Now, we can join students with courses using the WHERE clause:

SELECT Student.name AS student_name, Course.name AS course_name
FROM Student, Enrollment, Course
WHERE Student.id = Enrollment.student_id AND Enrollment.course_id = Course.id;

This query lists students and their enrolled courses, using the WHERE clause to define the join conditions.

4.3. CROSS JOIN

Using the WHERE clause to perform a CROSS JOIN:

SELECT Student.name AS student_name, Course.name AS course_name
FROM Student, Course
WHERE 1=1;

This query returns the Cartesian product of the Student and Course tables, similar to the CROSS JOIN example in the previous section. The WHERE $1=1$ condition is always true, so it doesn’t filter any rows.

4.4. FULL OUTER JOIN

We can use the LEFT JOIN and RIGHT JOIN with UNION to achieve a similar result as the FULL OUTER JOIN:

SELECT Student.name AS student_name, Department.name AS department_name
FROM Student, Department
WHERE Student.id = Department.id
UNION
SELECT Student.name, Department.name
FROM Student
RIGHT JOIN Department ON Student.id = Department.id
WHERE Student.id IS NULL;

This query attempts to combine all students and departments, including unmatched rows from both tables, simulating a FULL OUTER JOIN.

5. Detailed Comparisons

The primary difference between JOIN syntax and WHERE joins is how the relationships between tables are specified. The JOIN syntax is generally more explicit and readable, particularly for complex queries.

Let’s consider a more complex query that combines multiple tables. For instance, retrieving a list of students, their enrolled courses, and the corresponding departments. By using the JOIN syntax:

SELECT Student.name AS student_name, Course.name AS course_name, Department.name AS department_name
FROM Student
JOIN Enrollment ON Student.id = Enrollment.student_id
JOIN Course ON Enrollment.course_id = Course.id
JOIN Department ON Course.department_id = Department.id;

By using the WHERE syntax:

SELECT Student.name AS student_name, Course.name AS course_name, Department.name AS department_name
FROM Student, Enrollment, Course, Department
WHERE Student.id = Enrollment.student_id
AND Enrollment.course_id = Course.id
AND Course.department_id = Department.id;

In the JOIN syntax example, each join condition is associated with the relevant tables, making the query more readable and maintainable. The WHERE join example combines all tables in the FROM clause, which can become unmanageable as the number of tables and conditions increases.

6. Performance Considerations

Understanding the differences between JOIN syntax and WHERE joins in SQL is crucial for writing efficient, readable, and maintainable queries.

For simple queries, the WHERE join syntax can be quicker to write and easier to understand. Also, it is useful for understanding and maintaining legacy SQL code that uses this syntax.

However, the JOIN syntax offers several advantages in terms of clarity and best practices.

First of all, it is more explicit and easier to understand, especially as query complexity increases. It separates join conditions from filter conditions, making the query structure more intuitive. Moreover, while modern SQL engines optimize both JOIN syntax and WHERE joins effectively, using explicit JOIN statements can help the SQL engine generate better execution plans and improve query performance.

Furthermore, JOIN syntax supports various types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN), each suited for different scenarios.

7. Conclusion

Throughout this tutorial, we demonstrated how to use both JOIN syntax and WHERE with practical examples using a university database schema. Understanding and using the appropriate join type is essential for achieving the desired result.

By comparing these methods side-by-side, we illustrated the benefits of using JOIN syntax for complex queries.