INNER JOIN in SQL

An INNER JOIN in SQL is used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables. If there is no match, the rows are excluded from the result set. This is the most commonly used type of join in SQL.

Key Points

1. Matching Rows: INNER JOIN returns only the rows where there is a match in both tables.
2. Common Column: The tables being joined must have at least one common column, usually a primary key and foreign key.
3. Exclusion of Non-Matching Rows: Rows that do not have a match in both tables are excluded from the result set.
4. Alias Support: You can use table aliases to simplify queries.

Syntax

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

• column1, column2, ...: The columns you want to retrieve.
• table1, table2: The tables you want to join.
• common_column: The column that relates the two tables.

Examples

Suppose you have two tables: Employees and Departments.

Table: Employees

EmployeeID	Name	City	DepartmentID
1	Anand	Chennai	101
2	Bala	Coimbatore	102
3	Kavitha	Karaikal	103
4	Raj	Madurai	104
5	Kumar	Trichy	105

Table: Departments

DepartmentID	DepartmentName
101	HR
102	Finance
103	IT
104	Marketing
105	Sales

To retrieve employee names along with their department names:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result

Name	DepartmentName
Anand	HR
Bala	Finance
Kavitha	IT
Raj	Marketing
Kumar	Sales

Using INNER JOIN with WHERE Clause

You can combine INNER JOIN with the WHERE clause to filter the results further. For example, retrieve employees who work in the IT department.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentName = 'IT';

Result

Name	DepartmentName
Kavitha	IT

Using INNER JOIN with Multiple Tables

You can use INNER JOIN to join more than two tables. Suppose you have an additional table named Projects.

Table: Projects

ProjectID	ProjectName	DepartmentID
201	Project A	101
202	Project B	102
203	Project C	103

To retrieve employee names, their department names, and the projects they are working on:

SELECT Employees.Name, Departments.DepartmentName, Projects.ProjectName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
INNER JOIN Projects ON Departments.DepartmentID = Projects.DepartmentID;

Result

Name	DepartmentName	ProjectName
Anand	HR	Project A
Bala	Finance	Project B
Kavitha	IT	Project C

Practical Use Case

Suppose you have a table named Students and another table named Courses.

Table: Students

StudentID	Name	City	CourseID
1	Ram	Chennai	101
2	Karthik	Coimbatore	102
3	David	Bangalore	103

Table: Courses

CourseID	CourseName
101	Mathematics
102	Science
103	English

To retrieve student names along with the courses they are enrolled in:

SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

Result

Name	CourseName
Ram	Mathematics
Karthik	Science
David	English

Key Takeaways

1. INNER JOIN returns only the rows where there is a match in both tables.
2. It is used to combine rows from two or more tables based on a related column.
3. Non-matching rows are excluded from the result set.
4. You can use INNER JOIN with the WHERE clause to filter results further.
5. INNER JOIN can be used to join multiple tables in a single query.