Inner Join in MySQL

Inner Join in MySQL with Real-time Examples

In this article, I am going to discuss the Inner Join in MySQL with Real-time Examples. Please read our previous article where we discussed the Basics of Joins in MySQL. As part of this article, we are going to discuss the following pointers in detail.

  1. What is Inner Join?
  2. How to implement Inner Join in MySQL?
  3. When do we need to use Inner JOIN?
  4. Example to Understand Inner JOIN
  5. How to use Inner Join with WHERE Condition?
  6. How to Inner Join Three Tables in MySQL?
  7. How to join more than two tables using different joins?
What is Inner Join in MySQL?

The Inner Join is used to return only the matching rows from both the tables involved in the join. That means it eliminates the non-matching rows. The INNER JOIN is the most common type of join used in real-time applications. The following diagram shows the pictorial representation of Inner Join.

What is Inner Join in MySQL?

How to implement Inner Join in MySQL?

The following images show the syntax of Inner Join. As you can see in the below image, we can use either the INNER JOIN or JOIN keyword to perform the Inner Join operation in MySQL. That means if you use the JOIN keyword, then also it is going to perform Inner JOIN Operation.

How to implement Inner Join in MySQL?

Note: The INNER JOIN in MySQL returns the rows in the result set where the column value in a row of table1 is equal to the column value in a row of table2. The ON clause defines the columns and conditions to be evaluated in MySQL Inner Join.

When do we need to use Inner JOIN in MySQL?

If you want to retrieve all the matching rows from both the tables involved in the join by removing all the non-matching rows in the result set then you need to use the Inner Join. 

Examples to Understand Inner JOIN:

Let us understand how to implement Inner Join with Examples. To understand Inner Join, we are going to use the following Employee, Address, and Projects tables. Here, EmployeeId is the common column that is available in all the tables, and based on this column we are going to perform the Inner Join Operations.

When do we need to use Inner JOIN in MySQL?

Please use the following SQL Script to create the Company database and Employee, Address, and Projects tables with the required data. Here the EmployeeId column in the Address and Projects table is a foreign key that is referencing the EmployeeId column of the Employee Table.

CREATE DATABASE Company;
USE Company;

CREATE TABLE Employee (
  EmployeeId INT PRIMARY KEY,
  FullName VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL
);

INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26);

CREATE TABLE Projects (
 ProjectId INT PRIMARY KEY AUTO_INCREMENT,
    ProjectName VARCHAR(200) NOT NULL,
 EmployeeId INT,
    StartDate DATETIME,
    EndDate DATETIME,
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId)
);

INSERT INTO Projects (ProjectName, EmployeeId, StartDate, EndDate) VALUES 
('Develop Ecommerse Website from scratch', 1003, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)),
('WordPress Website for our company', 1002, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY)),
('Manage our Company Servers', 1007, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY)),
('Hosting account is not working', 1009, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY)),
('MySQL database from my desktop application', 1010, NOW(), DATE_ADD(NOW(), INTERVAL 15 DAY)),
('Develop new WordPress plugin for my business website', NULL, NOW(), DATE_ADD(NOW(), INTERVAL 10 DAY)),
('Migrate web application and database to new server', NULL, NOW(), DATE_ADD(NOW(), INTERVAL 5 DAY)),
('Android Application development', 1004, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)),
('Hosting account is not working', 1001, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY)),
('MySQL database from my desktop application', 1008, NOW(), DATE_ADD(NOW(), INTERVAL 15 DAY)),
('Develop new WordPress plugin for my business website', NULL, NOW(), DATE_ADD(NOW(), INTERVAL 10 DAY));

CREATE TABLE Address
(
    AddressId INT PRIMARY KEY AUTO_INCREMENT,
    EmployeeId INT,
    Country VARCHAR(50),
    State VARCHAR(50),
    City VARCHAR(50),
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId)
);

INSERT INTO Address (EmployeeId, Country, State, City) Values (1001, 'India', 'Odisha', 'BBSR');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1002, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1003, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1004, 'India', 'Odisha', 'Cuttack');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1005, 'India', 'Maharashtra', 'Nagpur');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1006, 'India', 'Odisha', 'Cuttack');
MySQL Inner Join Example:

Our requirement is to retrieve the EmployeeId, FullName, Department, Gender, ProjectName from the Employee and Projects tables. The query should return the following result set.

Examples to Understand Inner JOIN in MySQL

Following is the SQL Query that is Inner Joining the Employee and Projects tables based on the EmployeeID column and will give you the required results.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName
FROM Employee 
INNER JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId;

If you look at the output in the image, here, we got only 8 rows. We did not get those 3 rows that have the NULL value in the EmployeeId column of the Projects table. Instead of using the INNER JOIN keyword in MySQL, we can also use the JOIN keyword as shown in the below SQL Query and it will also give you the same result as the previous SQL Query.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName
FROM Employee 
JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId;

Note: INNER JOIN or JOIN means the same. But it’s always recommended to use INNER JOIN, as it explicitly specifies the type of join operation we are performing.

MySQL Inner Join with Where Condition:

Let us understand how to use Inner join along with the WHERE filtering condition in MySQL. Our requirement is to retrieve all the EmployeeId, FullName, Department, Gender, ProjectName from the Employee and Projects tables where the gender is Male. The following is the SQL Script.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName
FROM Employee 
INNER JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId
WHERE Employee.Gender = 'Male';

When you execute the above query, you will get the following result set which includes only Male employees who are currently assigning with projects.

MySQL Inner Join with WHERE Condition

Joining Three Tables in MySQL:

It is possible in MySQL to join more than two tables. Let us see and understand how to JOIN three tables. The following is the syntax in MySQL to join three tables.

Joining Three Tables in MySQL

Example: Inner Joining Employee, Projects, and Address tables

Now, our requirement is to fetch the details of such employees who are currently having any projects as well as they must have an address. The following SQL Query Inner Joining Employee, Projects, and Address tables to fetch the Employee details, projects, and their addresses.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName, Country, State, City
FROM Employee 
INNER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId
INNER JOIN Address ON Employee.EmployeeID = Address.EmployeeId;

When you execute the above SQL query, you will get the following result set. You can see, we get only four records, this is because the following four records exist in all three tables.

Inner Joining Employee, Projects, and Address tables

Joining Multiple Tables with Different Joining Condition in MySQL

It is also possible in MySQL to use Different types of Joins while joining more than two tables. For example, in the below SQL Query, the first joining operation (between Employee and Projects) is performed using Inner JOIN and the second join operation (between Employee and Address) is performed using LEFT JOIN in MySQL.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName, Country, State, City
FROM Employee 
INNER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId
LEFT JOIN Address ON Employee.EmployeeID = Address.EmployeeId;

When you execute the above SQL Query, you will get the following result set. In this case, for the left join between Employee and Address table, it will take null values for Country, State, and City column which does not have any reference.

Joining Multiple Tables with Different Joining Condition in MySQL

In the next article, I am going to discuss Left Outer Join in MySQL with real-time examples. Here, in this article, I try to explain the Inner Join in MySQL with Real-time Examples and I hope you enjoy this article. 

Leave a Reply

Your email address will not be published.