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:

1. Left outer join 
2. Right outer join 
3. 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

My Personal Notes arrow_drop_up

Save