Hello friends, in this article we will learn about CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, SELF JOIN and many more with examples.
JOIN are used to join two or more tables side by side to get set of rows.
To understand different types of join in SQL Server.
Lets create two table and insert some data.
CREATE TABLE dbo.tbFirstStudentList
(
StudentId int,
StudentName varchar(100)
)
Go
INSERT INTO dbo.tbFirstStudentList VALUES(1,'Santiago'),(2,'Daniel'),(3,'Jayden'),(4,'Agustín')
Go
INSERT INTO dbo.tbFirstStudentList VALUES(5,'Sofía'),(6,'Alysha'),(7,'John'),(10,'William'),(13,'Martina'),(14,'Liam')
Go
CREATE TABLE dbo.tbSecondStudentList
(
StudentId int,
StudentName varchar(100)
)
Go
INSERT INTO dbo.tbSecondStudentList VALUES(7,'John'),(2,'Daniel'),(8,'Robert'),(4,'Agustín')
Go
INSERT INTO dbo.tbSecondStudentList VALUES(9,'William'),(6,'Alysha'),(11,'Martina'),(12,'Liam')
Go
SELECT * FROM dbo.tbFirstStudentList
SELECT * FROM dbo.tbSecondStudentList
Go
OUTPUT: StudentId StudentName ----------- -------------------------- 1 Santiago 2 Daniel 3 Jayden 4 Agustín 5 Sofía 6 Alysha 7 John 10 William 13 Martina 14 Liam (10 rows affected) StudentId StudentName ----------- -------------------------- 7 John 2 Daniel 8 Robert 4 Agustín 9 William 6 Alysha 11 Martina 12 Liam (8 rows affected)
1) CROSS OR CARTESIAN JOIN (CJ)
Cross join or Cartesian Join select each row of first table one by one and combine each and every row for first table with every row of second table.
SELECT * FROM dbo.tbFirstStudentList, dbo.tbSecondStudentList
Go
--OR
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
CROSS JOIN dbo.tbSecondStudentList S
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ------------------ ----------- ------------------------------------------- 1 Santiago 7 John 2 Daniel 7 John 3 Jayden 7 John 4 Agustín 7 John 5 Sofía 7 John 6 Alysha 7 John 7 John 7 John 10 William 7 John 13 Martina 7 John 14 Liam 7 John 1 Santiago 2 Daniel 2 Daniel 2 Daniel 3 Jayden 2 Daniel 4 Agustín 2 Daniel 5 Sofía 2 Daniel 6 Alysha 2 Daniel 7 John 2 Daniel 10 William 2 Daniel 13 Martina 2 Daniel 14 Liam 2 Daniel 1 Santiago 8 Robert 2 Daniel 8 Robert 3 Jayden 8 Robert 4 Agustín 8 Robert 5 Sofía 8 Robert 6 Alysha 8 Robert 7 John 8 Robert 10 William 8 Robert 13 Martina 8 Robert 14 Liam 8 Robert 1 Santiago 4 Agustín 2 Daniel 4 Agustín 3 Jayden 4 Agustín 4 Agustín 4 Agustín 5 Sofía 4 Agustín 6 Alysha 4 Agustín 7 John 4 Agustín 10 William 4 Agustín 13 Martina 4 Agustín 14 Liam 4 Agustín 1 Santiago 9 William 2 Daniel 9 William 3 Jayden 9 William 4 Agustín 9 William 5 Sofía 9 William 6 Alysha 9 William 7 John 9 William 10 William 9 William 13 Martina 9 William 14 Liam 9 William 1 Santiago 6 Alysha 2 Daniel 6 Alysha 3 Jayden 6 Alysha 4 Agustín 6 Alysha 5 Sofía 6 Alysha 6 Alysha 6 Alysha 7 John 6 Alysha 10 William 6 Alysha 13 Martina 6 Alysha 14 Liam 6 Alysha 1 Santiago 11 Martina 2 Daniel 11 Martina 3 Jayden 11 Martina 4 Agustín 11 Martina 5 Sofía 11 Martina 6 Alysha 11 Martina 7 John 11 Martina 10 William 11 Martina 13 Martina 11 Martina 14 Liam 11 Martina 1 Santiago 12 Liam 2 Daniel 12 Liam 3 Jayden 12 Liam 4 Agustín 12 Liam 5 Sofía 12 Liam 6 Alysha 12 Liam 7 John 12 Liam 10 William 12 Liam 13 Martina 12 Liam 14 Liam 12 Liam (80 rows affected)
2) INNER JOIN (IJ)
It combines all rows of two or more tables side by side which satisfied the condition on related columns betweem them.
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
INNER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- --------------- ----------- ----------------- 2 Daniel 2 Daniel 4 Agustín 4 Agustín 6 Alysha 6 Alysha 7 John 7 John (4 rows affected)
3) OUTER JOIN OF 3 Types
3.1) Left Outer Join (LOJ)
3.2) Right Outer Join (ROJ)
3.3) Full Outer Join (FOJ)
3.1 LEFT OUTER JOIN (LOJ)
It retruns all the rows of first table (left).
Combines side by side second table(right) with its values if condition satisfied other wise null.
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
LEFT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ------------------- ----------- ------------- 1 Santiago NULL NULL 2 Daniel 2 Daniel 3 Jayden NULL NULL 4 Agustín 4 Agustín 5 Sofía NULL NULL 6 Alysha 6 Alysha 7 John 7 John 10 William NULL NULL 13 Martina NULL NULL 14 Liam NULL NULL (10 rows affected)
3.2 RIGHT OUTER JOIN (ROJ)
It retruns all the rows of second table (right).
Combines side by side first table(left) with its values if condition satisfied, other wise null.
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
RIGHT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ---------------- ----------- ------------- 7 John 7 John 2 Daniel 2 Daniel NULL NULL 8 Robert 4 Agustín 4 Agustín NULL NULL 9 William 6 Alysha 6 Alysha NULL NULL 11 Martina NULL NULL 12 Liam (8 rows affected)
3.3 FULL OUTER JOIN (FOJ)
It retruns all the rows of first table (left).
Combines side by side second table(right) with its values if condition satisfied other wise null.
Appliess union with
It retruns all the rows of second table (right).
Combines side by side first table(left) with its values if condition satisfied, other wise null.
In short [LEFT OUTER JOIN] UNION [RIGHT OUTER JOIN]
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
FULL OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ---------------- ----------- --------------- 1 Santiago NULL NULL 2 Daniel 2 Daniel 3 Jayden NULL NULL 4 Agustín 4 Agustín 5 Sofía NULL NULL 6 Alysha 6 Alysha 7 John 7 John 10 William NULL NULL 13 Martina NULL NULL 14 Liam NULL NULL NULL NULL 8 Robert NULL NULL 9 William NULL NULL 11 Martina NULL NULL 12 Liam (14 rows affected)
OR
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
LEFT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
UNION
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
RIGHT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- --------------- ----------- ------------ NULL NULL 8 Robert NULL NULL 9 William NULL NULL 11 Martina NULL NULL 12 Liam 1 Santiago NULL NULL 2 Daniel 2 Daniel 3 Jayden NULL NULL 4 Agustín 4 Agustín 5 Sofía NULL NULL 6 Alysha 6 Alysha 7 John 7 John 10 William NULL NULL 13 Martina NULL NULL 14 Liam NULL NULL (14 rows affected)
--FULL OUTER JOIN (WITH ALL ROWS) OR
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
LEFT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
UNION ALL
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
RIGHT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- --------------- ----------- -------------- 1 Santiago NULL NULL 2 Daniel 2 Daniel 3 Jayden NULL NULL 4 Agustín 4 Agustín 5 Sofía NULL NULL 6 Alysha 6 Alysha 7 John 7 John 10 William NULL NULL 13 Martina NULL NULL 14 Liam NULL NULL 7 John 7 John 2 Daniel 2 Daniel NULL NULL 8 Robert 4 Agustín 4 Agustín NULL NULL 9 William 6 Alysha 6 Alysha NULL NULL 11 Martina NULL NULL 12 Liam (18 rows affected)
SELF JOIN:
If we combine one table as left with same table as right is called self join. We can use self join to remove duplicate rows. update records of same table with its other dpendent columns.
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
INNER JOIN dbo.tbFirstStudentList S
ON F.StudentId = S.StudentId
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ----------------- ----------- -------------- 1 Santiago 1 Santiago 2 Daniel 2 Daniel 3 Jayden 3 Jayden 4 Agustín 4 Agustín 5 Sofía 5 Sofía 6 Alysha 6 Alysha 7 John 7 John 10 William 10 William 13 Martina 13 Martina 14 Liam 14 Liam (10 rows affected)
Some Extra example using null type.
Get all Records of dbo.tbFirstStudentList Which are not in dbo.tbSecondStudentList
SELECT * FROM dbo.tbFirstStudentList F
WHERE F.StudentId NOT IN (SELECT StudentId FROM dbo.tbSecondStudentList)
Go
--OR
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
LEFT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
WHERE S.StudentId IS NULL
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ---------------- ----------- -------------- 1 Santiago NULL NULL 3 Jayden NULL NULL 5 Sofía NULL NULL 10 William NULL NULL 13 Martina NULL NULL 14 Liam NULL NULL (6 rows affected)
Select all records of dbo.tbSecondStudentList Which are not in dbo.tbFirstStudentList
SELECT * FROM dbo.tbSecondStudentList
WHERE StudentId NOT IN (SELECT StudentId FROM dbo.tbFirstStudentList)
Go
--OR
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
RIGHT OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
WHERE F.StudentId IS NULL
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ----------------- ----------- ------------- NULL NULL 8 Robert NULL NULL 9 William NULL NULL 11 Martina NULL NULL 12 Liam (4 rows affected)
Select all students from both tables except with in each other.
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
FULL OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
WHERE F.StudentId IS NULL OR S.StudentId IS NULL
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ---------------- ----------- --------------- 1 Santiago NULL NULL 3 Jayden NULL NULL 5 Sofía NULL NULL 10 William NULL NULL 13 Martina NULL NULL 14 Liam NULL NULL NULL NULL 8 Robert NULL NULL 9 William NULL NULL 11 Martina NULL NULL 12 Liam (10 rows affected)
INNER JOIN USING FULL OUTER JOIN
SELECT F.*,S.* FROM dbo.tbFirstStudentList F
FULL OUTER JOIN dbo.tbSecondStudentList S
ON F.StudentId = S.StudentId
WHERE F.StudentId IS NOT NULL AND S.StudentId IS NOT NULL
Go
OUTPUT: StudentId StudentName StudentId StudentName ----------- ---------------- ----------- -------------- 2 Daniel 2 Daniel 4 Agustín 4 Agustín 6 Alysha 6 Alysha 7 John 7 John (4 rows affected)
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.