Inner Join vs Outer Join - GeeksforGeeks
Inner Join vs Outer Join
- Difficulty Level : Easy
- Last Updated : 09 May, 2017
What is 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
EnrollNoStudentNameAddress1001geek1geeksquiz11002geek2geeksquiz21003geek3geeksquiz31004geek4geeksquiz4
StudentCourse Table
CourseIDEnrollNo1100121001310011100221003
Following is join query that shows names of students enrolled in different courseIDs.
SELECT StudentCourse.CourseID,Student.StudentNameFROM StudentINNER JOIN StudentCourseON StudentCourse.EnrollNo = Student.EnrollNoORDER BY StudentCourse.CourseID
Note: INNER is optional above. Simple JOIN is also considered as INNER JOIN
The above query would produce following result.
CourseIDStudentName1geek11geek22geek12geek33geek1
What is the difference between inner join and outer join?
Outer Join is of 3 types
1) Left outer join
2) Right outer join
3) Full Join
1) Left outer join returns all rows of table on left side of join. The rows for which there is no matching row on right side, result contains NULL in the right side.
SELECT Student.StudentName,StudentCourse.CourseIDFROM StudentLEFT OUTER JOIN StudentCourseON StudentCourse.EnrollNo = Student.EnrollNoORDER BY StudentCourse.CourseID
Note: OUTER is optional above. Simple LEFT JOIN is also considered as LEFT OUTER JOIN
StudentNameCourseIDgeek4NULLgeek21geek11geek12geek32geek13
2) Right Outer Join is similar to Left Outer Join (Right replaces Left everywhere)
3) Full Outer Join Contains results of both Left and Right outer joins.