Explicit vs Implicit MySQL Joins

MySQL joins are used to combine rows from two or more tables based on a related column between them. MySQL, a popular relational database management system, offers two main approaches to perform joins: explicit and implicit. In this article, we will explore these two methodologies, understanding their syntax, use cases, and the implications for code readability and performance, when to use, and the Difference between these two approaches.

Explicit Joins

• Explicit joins are explicitly specified in the SQL query using the JOIN keyword followed by the type of join (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, etc.) and the condition for joining the tables.
• Explicit joins provide more control over the join operation and allow you to specify the exact conditions for joining the rows from different tables.
• Commonly used to combine data from multiple tables based on specific criteria.
• Clearly define the relationships between tables, making the query logic more transparent.
• Can be more efficient in some cases, especially when there are complex join conditions.

Syntax:

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

Examples of Explicit MySQL Joins

1. INNER JOIN

• An INNER JOIN retrieves rows that have matching values in both tables.Syntax:

Syntax:

SELECT column1, column2, …

FROM table1

INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

Suppose we have two tables: Employees and Departments. The employees table contains information about employees, and the departments table contains details about different departments in a company. The common column between these tables is department_id.

First we will create Employee and Department Tables then we will perform INNER JOIN between these table.

To create the Employee table CREATE TABLE command is used also we will add three columns inside the table. Following is the query to create the Employee table with three columns also we will add the data into the table using INSERT command.

-- Creating the employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT
);

Following is the query to insert the data into the table

-- Populating the employees table
INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, 'John Doe', 1),
(2, 'Jane Smith', 2),
(3, 'Bob Johnson', 1),
(4, 'Alice Brown', 2);

Output:

Similarly we will create Department table with two columns also we will insert data into Department Table.

Following is the query to create and insert the data into Departments table.

-- Creating the departments table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

Following is the query to insert the data into table.

-- Populating the departments table
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Engineering'),
(2, 'Marketing');" 

Output:

Till now we have created employees and Departments table. Now, let’s perform an explicit INNER JOIN to retrieve information about employees and their corresponding departments. Following is the query to this task.

SELECT
    employees.employee_id,
    employees.employee_name,
    employees.department_id,
    departments.department_name
FROM
    employees
INNER JOIN
    departments ON employees.department_id = departments.department_id;

Output

employee_id	employee_name	department_name
1	John Doe	Engineering
2	Jane Smith	Marketing
3	Bob Johnson	Engineering
4	Alice Brown	Marketing

Explanation: In the above example The INNER JOIN clause is used to combine rows from both the employees and departments tables based on the specified condition (employees.department_id = departments.department_id). SELECT statement specifies the columns you want to retrieve in the result set (employee_id, employee_name, and department_name). The result set will include only the rows where there is a match in both tables based on the department_id.

2. Left Join

• A LEFT JOIN retrieves all rows from the left table (table1) and the matching rows from the right table (table2).
• If there are no matching rows in the right table, the columns from the right table will be filled with NULL values.

Syntax:

SELECT column1, column2, …

FROM table1

LEFT JOIN table2 ON table1.column_name = table2.column_name;

Example:

To perform an explicit Left Join between the employees and departments tables based on the department_id column, Following is the SQL query:

SELECT 
          employees.employee_id, 
          employees.employee_name, 
          employees.department_id, 
          departments.department_name
FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;

The above query retrieves the employee_id, employee_name, department_id, and department_name from both tables. If there is a match on the department_id column, it includes the corresponding values from the departments table. If there is no match, it still includes the row from the employees table, but the columns from the departments table will be filled with NULL values. The LEFT JOIN ensures that all rows from the employees table are included in the result set.

Output:

employee_id	employee_name	department_id	department_name
1	John Doe	1	Engineering
2	Jane Smith	2	Marketing
3	Bob Johnson	1	Engineering
4	Alice Brown	2	Marketing

3. Right Join

The RIGHT JOIN returns all rows from the right table, even if there are no matching values in the left table.

Syntax:

SELECT column_list

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

Example:

To perform an explicit Right Join between the employees and departments tables based on the department_id column, Following is the SQL query:

SELECT 
    employees.employee_id, 
    employees.employee_name, 
    employees.department_id, 
    departments.department_name
FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;

The above SQL query is using a RIGHT JOIN to retrieve specific columns from both the employees and departments tables where there is a match based on the common department_id column.

Output:

The above result includes columns from both tables based on the RIGHT JOIN condition. The rows are determined by the departments table, so all departments are included in the result. For the matching rows, employee information is included. For example, department_id 1 (Engineering) has two employees (John Doe and Bob Johnson), and department_id 2 (Marketing) has two employees (Jane Smith and Alice Brown). If there is no match in the employees table for a particular department, NULL values are returned for the columns from the employees table (as seen in the last row for department_id 2).

Implicit Joins

Implicit joins are joins that are specified implicitly in the SQL query using the WHERE clause. The WHERE clause is used to filter the rows in the table based on a condition. If the condition in the WHERE clause involves columns from two or more tables, then an implicit join is performed.

• Implicit joins are performed automatically by the database engine based on the relationship defined between tables.
• In MySQL, implicit joins are primarily used for joining tables that have a foreign key relationship.
• The foreign key column in one table references the primary key column in another table, establishing a parent-child relationship.
• The database automatically joins the tables using the foreign key and primary key values, without the need for an explicit JOIN clause in the query.
• Typically used to retrieve related data from multiple tables based on foreign key relationships.
• Simpler and more concise syntax compared to explicit joins.
• May be less efficient than explicit joins in certain scenarios, especially when there are complex join conditions.

Syntax:

SELECT columns

FROM table1

WHERE table1.id = table2.id;

Examples of Implicit Joins

1. Implicit INNER JOIN

First we will create two tables students and courses tables. then we will perform implicit inner join on the tables.

Now, we will create the students table inside this table we will add student_id,student_name, and course_id. Following is the SQL query:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255),
    course_id INT
);

Now we will insert the data into the table following is the SQL query.

INSERT INTO students (student_id, student_name, course_id) VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 101),
(4, 'David', 103);

Output:

student_id	student_name	course_id
1	Alice	101
2	Bob	102
3	Charlie	101
4	David	103

Similarly we will create the courses table.Following is the SQL query.

Table creation

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(255)
);

Data insertion

INSERT INTO courses (course_id, course_name) VALUES
(101, 'Mathematics'),
(102, 'History'),
(103, 'Physics');

Output:

course_id	course_name
101	Mathematics
102	History
103	Physics

So till now we have created two tables students and courses tables. now we will perform implicit inner join on students and courses table.Following is the query to perform the implicit join.

SELECT students.student_id, students.student_name, students.course_id, courses.course_name
FROM students, courses
WHERE students.course_id = courses.course_id;

In above query FROM clause lists both the students and courses tables and WHERE clause implicitly specifies the join condition by equating the course_id in the students table to the course_id in the courses table.

Output:

student_id	student_name	course_id	course_name
1	Alice	101	Mathematics
2	Bob	102	History
3	Charlie	101	Mathematics
4	David	103	Physics

Explanation: This result set combines information from both the students and courses tables, showing the student_id, student_name, course_id, and course_name for each student based on the matching course_id. The query essentially links the two tables on the common course_id column, providing a list of students along with the names of the courses they are enrolled in.

Comparison of Explicit and Implicit Joins

The following table compares explicit and implicit joins

Feature	Explicit Joins	Implicit Joins
Syntax	JOIN keyword	WHERE clause
Join type	Specified explicitly	Inferred from the WHERE clause
Join condition	Specified explicitly	Inferred from the WHERE clause
Performance	Usually faster	Usually slower
Readability	Usually more readable	Usually less readable

Conclusion

Explicit joins should be used when you want to have more control over the join operation. For example, you might use an explicit join if you want to specify a specific join type or if you want to use a complex join condition. Implicit joins should be used when you want to keep your SQL queries simple and easy to read. Implicit joins can also be used to improve performance in some cases.