INNER JOIN
INNER JOIN is used to return only the records that have matching values in both tables.
It is one of the most commonly used JOINs in SQL Server. When two tables are related through a common column, INNER JOIN can be used to retrieve matching information from both tables.
What is INNER JOIN?
An INNER JOIN returns rows when there is a matching value in both the left table and the right table.
If a record exists in one table but there is no matching record in the other table, that record is not included in the result.
INNER JOIN Syntax
SELECT columns
FROM Table1
INNER JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn;
Example Tables
Students Table
| StudentId | StudentName | CourseId |
|---|---|---|
| 101 | Rahul | 1 |
| 102 | Priya | 2 |
| 103 | Amit | 1 |
| 104 | Neha | 3 |
| 105 | Rohan | 5 |
Courses Table
| CourseId | CourseName |
|---|---|
| 1 | .NET Full Stack |
| 2 | Java Full Stack |
| 3 | Python Full Stack |
| 4 | Software Testing |
Common Column
In this example, CourseId is the common column between the Students and Courses tables.
Students.CourseId = Courses.CourseId
Basic INNER JOIN Example
To display student names along with their course names, we can use the following query:
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.CourseId = Courses.CourseId;
Result
INNER JOIN returns only those students whose CourseId exists in the Courses table.
| StudentName | CourseName |
|---|---|
| Rahul | .NET Full Stack |
| Priya | Java Full Stack |
| Amit | .NET Full Stack |
| Neha | Python Full Stack |
INNER JOIN with WHERE
INNER JOIN can be combined with a WHERE clause to filter the matching records.
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.CourseId = Courses.CourseId
WHERE Courses.CourseName = '.NET Full Stack';
This returns only students enrolled in the .NET Full Stack course.
INNER JOIN using Table Aliases
Table aliases can make JOIN queries shorter and easier to read.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
INNER JOIN Courses AS C
ON S.CourseId = C.CourseId;
Here, S represents Students and C represents Courses.
Selecting Specific Columns
We do not always need to select every column from both tables. We can select only the required columns.
SELECT
S.StudentId,
S.StudentName,
C.CourseName
FROM Students AS S
INNER JOIN Courses AS C
ON S.CourseId = C.CourseId;
INNER JOIN with Multiple Tables
INNER JOIN can also be used to combine more than two tables.
SELECT
S.StudentName,
C.CourseName,
T.TrainerName
FROM Students AS S
INNER JOIN Courses AS C
ON S.CourseId = C.CourseId
INNER JOIN Trainers AS T
ON C.TrainerId = T.TrainerId;
This example demonstrates how multiple related tables can be combined using INNER JOIN.
INNER JOIN vs JOIN
In SQL Server, JOIN without a specified JOIN type is treated as an INNER JOIN.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
JOIN Courses AS C
ON S.CourseId = C.CourseId;
The above query produces the same type of result as an INNER JOIN.
Important Points
- INNER JOIN returns matching records from both tables.
- Non-matching records are excluded.
- The ON clause defines the relationship between the tables.
- INNER JOIN can be combined with WHERE, ORDER BY and other SQL clauses.
- Table aliases make complex JOIN queries easier to read.
- JOIN without a specified type means INNER JOIN.
Summary
INNER JOIN combines two or more related tables and returns only the rows that have matching values in both tables. The ON clause specifies the relationship between the tables. INNER JOIN is commonly used to retrieve related information from normalized database tables.