MySQL Inner Join

MySQL Inner Join type returns the records or rows present in both tables If there is at least one match between columns. Or, we can simply say, MySQL Inner Join returns the rows (or records) present in both tables as long as the condition after the ON Keyword is TRUE.

The MySQL Inner is the default join. So it is optional for you to use INNER keywords. Let us see the visual representation of it for better understanding.

From the above screenshot, you can easily understand that the Inner join only displays the matching records from Table A and Table B (Like an Intersect in Mathematics)

MySQL Inner Join Syntax

The basic syntax of the MySQL Inner Join is as shown below:

SELECT Table1.Column(s), Table2.Column(s)
FROM Table1
 INNER JOIN
     Table2 ON
   Table1.Common_Column = Table2.Common_Column

--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s)
FROM Table1
 JOIN Table2 ON
    Table1.Common_Column = Table2.Common_Column

For this MySQL Inner Join example, we are going to use the Employ and Department tables present in our company Database. Data present in the employ is:

Data present in the Department

The following is the list of ways to use this for combining two tables or getting information (records) from two or more tables.

MySQL Inner Join Select * Example

The following query display all the columns present in the employ and Department tables

eUSE company;
SELECT * FROM employ
   INNER JOIN department
      ON employ.DeptID = department.DeptID;

If you observe the below screenshot, Although there are 15 records in the employ table, Inner join is displaying 10 records. It is because DeptID values for the remaining 5 records (i.e., EmpID numbers 4, 5, 9, 11, and 15) in the MySQL employ table are NULLS.

NOTE: The DeptID column is repeated twice, which might be annoying to the end-user. To avoid unwanted columns, select the individual column names. Please avoid the SELECT * Statement.

MySQL Inner Join Select Few Columns

As we said before, please place the required columns after the SELECT Statement to avoid displaying unwanted columns.

USE company;
SELECT First_Name, Last_Name, Education, 
       DepartmentName, Standard_Salary,
       Yearly_Income, Sales
FROM employ
	INNER JOIN department
		ON employ.DeptID = department.DeptID;

Ambiguous Columns in MySQL Inner Join

The above query runs perfectly as long as the column names from both the employ and Department tables are different. What happens if they have the same column names? Well, with the above-specified method, you end up in a mess. Let us see how to fix the issue.

Let me show you one practical example. As you can see, we are using the same inner join query. But, we added DepID from the department table as an additional column.

USE company;
SELECT First_Name, Last_Name, Education, 
	DeptID, DepartmentName, Standard_Salary,
        Yearly_Income, Sales
FROM employ
	INNER JOIN department
		ON employ.DeptID = department.DeptID;

It is throwing an error: Ambiguous column DeptID. It is because the DeptID column is present in both tables and the query doesn’t know which column you are asking to display.

Solve Ambiguous Columns Error

To resolve this kind of issue, always use the table name before the column name.

The following query uses the ALIAS table name before the column names. By this approach, we can inform the server that we are looking for DepID belonging to the department table. We can simply write the above query as:

USE company;
SELECT  emp.First_Name, 
	emp.Last_Name, 
        emp.Education, 
	dept.DeptID, dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
	INNER JOIN department AS dept
		ON emp.DeptID = dept.DeptID;

NOTE: It is always best practice to use the table name before the Column name in Joins (avoids confusion). For example, SELECT employ.First_Name

MySQL Inner Join and using Keyword

If the name of the common column in both tables is the same, then use the USING keyword. The above query can also be returned as:

USE company;
SELECT  emp.First_Name, 
	emp.Last_Name, 
        emp.Education, 
	dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
	INNER JOIN department AS dept
		USING (DeptID); -- Or use standard way

MySQL Inner Join Multiple Conditions

Up to now, we are always showing the = operator as the condition to join the employ and department table. But you can replace = with < (less than), > (greater than), or not equal to operators.

In this example, we are using two conditions to connect those tables. First, DeptId should match, and Yearly Income should be less than the standard salary.

USE company;
SELECT  emp.First_Name, 
	emp.Last_Name, 
        emp.Education, 
	dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
   INNER JOIN department AS dept
	ON emp.DeptID = dept.DeptID AND
           emp.Yearly_Income < dept.Standard_Salary;

MySQL Inner Join Where Clause Example

In this example, we show how to use the where clause to apply the filter along with the Inner Join. I suggest you refer Where Clause article.

USE company;
SELECT  emp.First_Name, 
	emp.Last_Name, 
        emp.Education, 
	dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
	INNER JOIN department AS dept
		ON emp.DeptID = dept.DeptID
WHERE Yearly_Income > 900000;

MySQL Inner Join and Order By Caluse

Let me show you, How to write a query using the command prompt. In this example, we show how to use the Order By clause to sort data along with this. I suggest you refer to the Order By article.

USE company;
SELECT  emp.First_Name, 
	emp.Last_Name, 
        emp.Education, 
	dept.DepartmentName, dept.Standard_Salary,
        emp.Yearly_Income, emp.Sales
FROM employ AS emp
	INNER JOIN department AS dept
		ON emp.DeptID = dept.DeptID
ORDER BY First_Name, Last_Name;