Inner Join vs Outer Join

3

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

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

Following is join query that shows 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 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.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)

3) Full Outer Join Contains results of both Left and Right outer joins.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above

GATE CS Corner    Company Wise Coding Practice

Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.

Recommended Posts:



3 Average Difficulty : 3/5.0
Based on 3 vote(s)










Writing code in comment? Please use ide.geeksforgeeks.org, generate link and share the link here.