SQL INNER JOIN: The Complete Guide

When the condition is satisfied, column values for each matched pair of rows of two tables are combined into a result row. You can also check out the SQL LEFT JOIN tutorial on this blog.

SQL INNER JOIN

SQL Inner join combines records from two or more tables in a database. The inner join is used to select all rows from tables for the match between the columns in tables.

SQL INNER JOIN is based on the concept of EQUI JOINS. EQUI JOINS are those who use a comparison operator (=) for combining records from two or more tables.

The shaded part above Shows the common records between both the tables.

The inner join is a widely used SQL joins in SQL Database. SQL inner join clause allows us to query the data from two or more related tables. SQL INNER JOIN keyword selects records that have matching values in both tables.

The INNER JOIN keyword selects all the rows from both tables as long as there is a match between the columns. If there are records in the “tableA” table that do not have matches in “tableB,” these rows will not be shown!

Syntax

Select columns from Table_1 INNER JOIN Table_2 
on Table_1.column = Table_2.column;

So, in the above statements, columns represent the column names of the tables.

Table_1 and Table_2 are the names of the tables.

And the condition i.e Table_1.column = Table_2.column is used to compare the columns which are common in both the tables.

In the above syntax, the query has retrieved data from both Table_1 and Table_2 tables:

1. First, specify the main table (Table_1) in the FROM clause.
2. The second table contains an INNER JOIN clause (Table_2) and a join predicate. The only rows that cause a join predicate to evaluate TRUE are included in a result set.

The INNER JOIN clause compares each row of the table Table_1 with rows of Table_2 to find all the pairs of rows that satisfy a join predicate.

If a join predicate evaluates to TRUE, the column values of the matching rows of Table_1 and Table_2 are combined into a new row and included in the result set.

Let’s see an example of the above statements.

If you do not know how to create a table in SQL, you can check out how to create a table and insert values in the table in this blog.

Table_1: Employee

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

 

Table_2: Department

Dept_no	Emp_id
123	301
214	401
125	505

 

Table_3: Job

Dept_id	Job
123	Data Scientist
214	App Developer
125	Web Developer

 

Now, the query for the inner join is the following.

Select Department.Dept_no, Employee.Emp_name, Employee.City, Employee.Salary 
from Department 
INNER JOIN Employee on Department.Emp_id = Employee.Emp_id

See the output below.

Explanation

As you can see that the common records between the columns of employee and department table are shown.

JOIN Three Tables

See the following SQL INNER JOIN Query.

Select Department.Dept_no, Employee.Emp_name, Employee.City, Job.Job 
from((Department INNER JOIN Employee 
ON Department.Emp_id = Employee.Emp_id)
INNER JOIN Job 
ON Department.Dept_no = Job.Dept_id);

Output

Explanation

Here, the condition is checked between the department and employee table, and then it is checked between the Department and Job table. Then the common records between all those tables are displayed.

We are displaying all columns of the Employee and Department table using INNER JOIN.

Select * from Employee 
INNER JOIN Department ON Department.Emp_id = Employee.Emp_id;

See the below output.

Note

1. SQL is not Case Sensitive.
2. While checking for equality conditions, column names of both tables don’t need to be the same, and they can be different. So the only thing checked is a record within those particular columns.
3. The INNER JOIN in SQL joins two tables according to matching specific criteria using a comparison operator.

Difference between INNER JOIN and OUTER JOIN

The INNER JOIN is such type of join that returns all the rows from both the participating tables where a key record of one table is equal to the key records of another table.

Inner Join required a comparison operator to match rows from the participating tables based on both tables’ common field or column.

Whereas an OUTER JOIN returns all the rows from the participating tables that satisfy the condition, those rows that do not match the condition will appear in this operation. This result set can appear in three types of format –

The first one is the LEFT OUTER JOIN. This join output includes all the rows from the left table of the JOIN clause and unmatched rows from the right table with NULL values for selected columns.

The second one is the RIGHT OUTER JOIN. In this join, the output includes all the rows from the right of the JOIN cause and unmatched rows from a left table with NULL values for selected columns.

The last one is in FULL OUTER JOIN. This join includes the matching rows from the left and right tables of the JOIN clause and the unmatched rows from the left and the right table with NULL values for selected columns.

That’s it for this tutorial.