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:

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:

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.

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.