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

EmployeeIDNameCityDepartmentID
1AnandChennai101
2BalaCoimbatore102
3KavithaKaraikal103
4RajMadurai104
5KumarTrichy105

Table: Departments

DepartmentIDDepartmentName
101HR
102Finance
103IT
104Marketing
105Sales

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

NameDepartmentName
AnandHR
BalaFinance
KavithaIT
RajMarketing
KumarSales

​
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

NameDepartmentName
KavithaIT

​
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

ProjectIDProjectNameDepartmentID
201Project A101
202Project B102
203Project C103

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

NameDepartmentNameProjectName
AnandHRProject A
BalaFinanceProject B
KavithaITProject C

​
Practical Use Case

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

Table: Students

StudentIDNameCityCourseID
1RamChennai101
2KarthikCoimbatore102
3DavidBangalore103

Table: Courses

CourseIDCourseName
101Mathematics
102Science
103English

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

NameCourseName
RamMathematics
KarthikScience
DavidEnglish

​
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.