Understanding INNER JOIN in SQL: A Complete Guide

An inner join returns only the rows where there is a match in both tables.

The most important and frequently used of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax: Inner Join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

OR

SELECT ColumnList
FROM LeftTableName
JOIN_TYPE RightTableName
ON JoinCondition

Table 1: Left Table: Subject

Table 2: Right Table: Chapter

Example: Left Join

Code:

SELECT *
FROM Subjects
INNER JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId;

Output:

The above code will produce the following result-

You can use aliases like below, also you can select specific columns which is required.

Example: using alias

Code:

SELECT A.SubjectId,A.SubjectName, B.ChapterName
FROM Subjects AS A
INNER JOIN Chapters AS B
ON A.SubjectId = B.SubjectId;

Output:

The above code will produce the following result-

Prerequisite Codes

Code:

  

USE SQLExamples

DROP TABLE Subjects
DROP TABLE Chapters

CREATE TABLE Subjects(
	SubjectId INT,	
	SubjectName VARCHAR(30)
)


INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP')

SELECT * FROM Subjects

CREATE TABLE Chapters(
	ChapterId INT,	
	ChapterName VARCHAR(30),
	SubjectId int,
)
  

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1), 
(2, 'Datatypes Java', 2) 

SELECT * FROM Subjects
SELECT * FROM Chapters