Joins in SQL Server

Example:

Code:

 

-- Join Examples

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 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 Java', 2) 

SELECT * FROM Subjects
SELECT * FROM Chapters

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

Code: Left Join

  
-- Left Join
DELETE FROM Subjects
DELETE FROM Chapters

INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java') 

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 Subjects
SELECT * FROM Chapters

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

 -- Example 2 - left join
 DELETE FROM Subjects
 DELETE 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) 

SELECT * FROM Subjects
SELECT * FROM Chapters

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

Code: Right Join

 
-- Right Join

 DELETE FROM Subjects
 DELETE 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)
 
SELECT * FROM Subjects
SELECT * FROM Chapters

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

-- Right Join Example 2
 DELETE FROM Subjects
 DELETE FROM Chapters

 INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java')

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 Subjects
SELECT * FROM Chapters

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

Code: Full Outer Join

 

-- Full Outer Join
 DELETE FROM Subjects
 DELETE FROM Chapters

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

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),
(9, 'Datatypes Ruby', 5),
(10, 'Datatypes Ruby', 5)

SELECT * FROM Subjects
SELECT * FROM Chapters

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