Skip to content
Related Articles

Related Articles

Inner Join vs Outer Join

Improve Article
Save Article
  • Difficulty Level : Easy
  • Last Updated : 09 Jun, 2022
Improve Article
Save Article

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

EnrollNoStudentNameAddress
1001geek1geeksquiz1
1002geek2geeksquiz2
1003geek3geeksquiz3
1004geek4geeksquiz4

StudentCourse Table

CourseIDEnrollNo
11001
21001
31001
11002
21003

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.

CourseIDStudentName
1geek1
1geek2
2geek1
2geek3
3geek1

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

StudentNameCourseID
geek4NULL
geek21
geek11
geek12
geek32
geek13

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
Related Articles

Start Your Coding Journey Now!