Inner Join vs Outer Join
An SQL Join is used to combine data from two or more tables based on a common field between them. For example, consider the following two tables.
Student Table
EnrollNo | StudentName | Address |
---|---|---|
1001 | geek1 | geeksquiz1 |
1002 | geek2 | geeksquiz2 |
1003 | geek3 | geeksquiz3 |
1004 | geek4 | geeksquiz4 |
StudentCourse Table
CourseID | EnrollNo |
---|---|
1 | 1001 |
2 | 1001 |
3 | 1001 |
1 | 1002 |
2 | 1003 |
Inner Join / Simple join:
In an INNER join, it allows retrieving data from two tables with the same ID.
Syntax:
SELECT COLUMN1, COLUMN2 FROM
[TABLE 1] INNER JOIN [TABLE 2]
ON Condition;
The following is a join query that shows the names of students enrolled in different courseIDs.
SELECT StudentCourse.CourseID,Student.StudentName FROM Student INNER JOIN StudentCourse ON StudentCourse.EnrollNo = Student.EnrollNo ORDER BY StudentCourse.CourseID;
Note: INNER is optional above. Simple JOIN is also considered as INNER JOIN The above query would produce following result.
CourseID | StudentName |
---|---|
1 | geek1 |
1 | geek2 |
2 | geek1 |
2 | geek3 |
3 | geek1 |
What is the difference between inner join and outer join?
Outer Join is of three types:
- Left outer join
- Right outer join
- Full Join
1. Left outer join returns all rows of a table on the left side of the join. For the rows for which there is no matching row on the right side, the result contains NULL on the right side.
Syntax:
SELECT T1.C1, T2.C2
FROM TABLE T1
LEFT JOIN TABLE T2
ON T1.C1= T2.C1;
SELECT Student.StudentName,StudentCourse.CourseID FROM Student LEFT OUTER JOIN StudentCourse ON StudentCourse.EnrollNo = Student.EnrollNo ORDER BY StudentCourse.CourseID;
Note: OUTER is optional above. Simple LEFT JOIN is also considered as LEFT OUTER JOIN
StudentName | CourseID |
---|---|
geek4 | NULL |
geek2 | 1 |
geek1 | 1 |
geek1 | 2 |
geek3 | 2 |
geek1 | 3 |
2. Right Outer Join is similar to Left Outer Join (Right replaces Left everywhere).
Syntax:
SELECT T1.C1, T2.C2
FROM TABLE T1
RIGHT JOIN TABLE T2
ON T1.C1= T2.C1;
Example:
SELECT Student.StudentName, StudentCourse.CourseID
FROM Student
RIGHT OUTER JOIN StudentCourse
ON StudentCourse.EnrollNo = Student.EnrollNo
ORDER BY StudentCourse.CourseID;
3. Full Outer Join contains the results of both the Left and Right outer joins. It is also known as cross join. It will provide a mixture of two tables.
Syntax:
SELECT * FROM T1
CROSS JOIN T2;
Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above
Please Login to comment...