JOIN is one of the fundamental units of relational database system. Generally JOIN is used to combine related data from many tables to produce result.
There are different types of JOIN available,
- Inner Join
- Outer Join
- Cross Join
- Full Join
In this article, I have used few examples to demonstrate inner and outer joins. before dive into the examples, lets see the definition of inner and outer joins.
Inner Join, The default join in SQL Server is inner join. Inner join returns all matching records from the given data-sets. The match is identified using predicates.
Outer join can be classified into Left outer join and Right outer join.
Left outer join returns all records from left table, at same time, it brings all matching rows from right table for the given predicate and NULL appears in right side when no matching row exists.
Left outer join can also be defined as “result set of inner join + missed records from left table with NULL in the right hand side”
Right outer join returns all records from right table, at the same time, it brings all matching rows from left table for the given predicate, and NULL appears in left side when no matching row exists.
Right outer join can also defined as “result set of inner join + missed records from right table with NULL in the left hand side”
Below example used to illustrate the “join”
Let’s assume we have two data-sets, one contains the student’s details of class “A” and other contains exam results details. This has been illustrated using Venn diagram, however Venn diagram shouldn’t be used to represent table since a table can have many group of related data. Just to give a clarity and visual representation, I have used Venn diagram.
There are six students in class “A” which represented in the blue circle. But only two students from class “A” passed in exam. The green circle contains students who passed the exam, in this case, two students from Class “A” and one student from other class.
From here, let’s go by some questions.
Q. Find out the students from class “A” who passed in exam?
In order to answer this question, the best suitable join is inner join. Because, we need to extract only the matching records from both datasets. As shown in the figure, each row in the CLASS table is matched with all rows of the EXAM table using student_id predicate. When the match is found the record will be copied down.
The corresponding transact sql code is :
Select * from Class a join Exam b On a.student_id = b.student_id
Q. Find out the students from class “A” who failed in exam?
This can be answered using the “Left Outer Join”. Though there are different ways to get the result, I will be using left outer join since this article about join.
When joining CLASS and EXAM table using left outer join, we get all the records from CLASS table and matching records from EXAM table and NULL for the unmatched rows.
In order to answer the question, We need to extract all records where student_id is null in right side table. Referring to the below picture, you will get better understating of exactly how left outer join works.
Select * from Class a left outer join Exam b On a.student_id = b.student_id where b.student_id is null
Q. Find out the students who passed the exam but not studied in class “A” ?
This can be answered using the “Right Outer Join”.
When joining EXAM and CLASS table using right outer join, we get all the records from EXAM table and matching records from CLASS “A” table and NULL for the unmatched rows.
Select * from Class a right outer join Exam b On a.student_id = b.student_id where a.student_id is null