Joins in SQL Server in the presence of Primary and Foreign Key

Example:

Code:

-- Join Examples with primary and foreign key

USE SQLExamples

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


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

SELECT * FROM Subjects

CREATE TABLE Chapters(
	ChapterId INT PRIMARY KEY,	
	ChapterName VARCHAR(30),
	SubjectId int,
	FOREIGN KEY (SubjectId) REFERENCES Subjects(SubjectId)	 
)

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2),
(4, 'Datatypes Java', 2), 
(5, 'Introduction Python', 3),
(6, 'Datatypes Python', 3), 
(7, 'Introduction PHP', 4),
(8, 'Datatypes PHP', 4)

SELECT * FROM Chapters

Code: Inner Join

-- Inner Join
DELETE FROM Subjects
DELETE FROM Chapters

SELECT * FROM Subjects
SELECT * FROM Chapters

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


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

Code: Inner Join

 

SELECT * FROM Subjects
SELECT * FROM Chapters

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


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

Code: Left Join

-- Left Join
SELECT * FROM Subjects
SELECT * FROM Chapters

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

Code: Right Join

 
-- Right Join
SELECT * FROM Subjects
SELECT * FROM Chapters

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

-- Full Outer Join
SELECT * FROM Subjects
SELECT * FROM Chapters

SELECT * 
FROM Subjects
FULL OUTER JOIN Chapters
ON Subjects.SubjectId = Chapters.SubjectId