Explicit vs Implicit MySQL Joins

MySQL joins combined rows from two or more tables based on a related column. 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.

Features of Explicit Joins:

• Provides more control over the join operation and allows you to specify exact conditions for joining rows from different tables.
  
• It is 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:

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.

Creating the employees table:

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

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.

Creating the departments table:

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

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:

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:

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:

Explanation: 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.

Features of Implicit Joins:

• 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.

Creating the students table:

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

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

Output:

Creating the courses table:

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

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

Output:

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:

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

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.

muzamil79

Follow

Improve

Article Tags :

• Geeks Premier League
• Databases
• MySQL
• Geeks Premier League 2023