Skip to main content

Documentation Index

Fetch the complete documentation index at: https://rajanand.org/llms.txt

Use this file to discover all available pages before exploring further.

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.