MySQL Inner Join

MySQL Inner Join is one of the Join Type, which 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 join is the default join. So it is optional for you to use INNER Keyword. Let us see the visual representation of the Inner join for better understanding.

From the above screenshot, you can easily understand that the MySQL 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 Inner Join in MySQL is as shown below:

-- MySQL Inner Join Syntax
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 Employ, and Department tables present in our company Database. Data present in employ table is:

Data present in the Department Table

MySQL Inner Join Examples

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

Inner Join Select * Example

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

-- Inner Join in MySQL Example
USE 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, MySQL 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 repeated twice, which might be annoying to the end-user. To avoid unwanted columns, select the individual column names. Please avoid SELECT * Statement in Inner Join.

MySQL Inner Join Select Few Columns

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

-- Inner Join in MySQL Example
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 employ, and Department tables are different. What happens if they had 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 query. But, we added DepID from the department table as an additional column.

-- Inner Join in MySQL Example
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 the tables, and the query doesn’t know which column you are asking to display.

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

The following MySQL Inner Join query is using 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:

-- Inner Join in MySQL Example
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

Inner Join using Keyword

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

-- Inner Join in MySQL Example
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 Inner Join example, we are using two conditions to join those tables. First, DeptId should match, and Yearly Income should be less than the standard salary.

-- Inner Join in MySQL Example
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;

Inner Join Where Clause Example

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

-- Inner Join in MySQL Example
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;

Inner Join Command prompt Example

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

-- Inner Join in MySQL Example
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;