INNER JOIN in SQL
Table of Content:
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
SubjectId |
SubjectName |
1 |
C |
2 |
Java |
3 |
Python |
4 |
PHP |
Table 2: Right Table: Chapter
ChapterId |
ChapterName |
SubjectId |
1 |
Introduction C |
1 |
2 |
Datatypes Java |
2 |
Example: Left Join
Code:
SELECT * FROM Subjects INNER JOIN Chapters ON Subjects.SubjectId = Chapters.SubjectId;
Output:
The above code will produce the following result-
SubjectId |
SubjectName |
ChapterId |
ChapterName |
SubjectId |
1 |
C |
1 |
Introduction C |
1 |
2 |
Java |
2 |
Datatypes Java |
2 |
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-
SubjectId |
SubjectName |
ChapterName |
1 |
C |
Introduction C |
2 |
Java |
Datatypes Java |
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