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

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:

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

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.