Skip to content
Related Articles

Related Articles

Inner Join vs Outer Join

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

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
Recommended Articles
Page :

Start Your Coding Journey Now!