In this article, we will see the different types of SQL joins with practical examples. This is one of the most important topics in SQL.
SQL joins are basically 4 types.
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
These joins are used to combine results from two are more tables.
Inner join query will retrieve the matching records from both the tables and Non-matching records will be ignored.
It means the matching records from table 1 and the matching records from table 2 will be retrieved.
The definition and syntax of inner Join have given in the below diagram.
Now let us see the use of Inner join with real-time example.
Let us consider the two tables Student and Class which are given below. It has student information and associated class information for a specific student. You can see the below diagram it has a primary key and foreign key relationship between these two tables.
We want to retrieve the records from both the tables based on the primary key and foreign key relationships.
Let us consider we would like to retrieve the student information who are belonging to the Database class.
In order to retrieve the results for the mentioned query, we can write an SQL query as below.
The above query will retrieve the below result set.
Left Outer Join
Left Outer Join query will retrieve records from the left table and which has corresponding records from the right table.
The records of the Left table which have no entries in the Right table will be null over there.
In other words, this query will retrieve all the records from table 1 and which as matching records from table 2
Now let us see the use of SQL Joins Left Outer Join with real-time example.
Let us consider the same example Student and class. Below is our data as you can see, check out the data none of the students is enrolled for Frontend class.
See the below illustration to understand much better. Student and Class entities are joined together and then join these two.
I want to know everything about all the students. If there in class I want to know which classes there are In. If I use left outer join I will get everything on the left table.
See the Dan, he is not enrolled for any class but still, he is part of the left table.
The query will look like this
Right Outer Join
This Join query will retrieve all the records from the Right table and which has corresponding records from the left table.
The records of the Right table which has no entries in the Left table will be null.
In other words, the query will fetch the records from table 2 and which has matching records from table 1
Now let us see the use of SQL Joins right outer join with a real-time example.
Let us consider the same example Student and Class. Here I want to know everything about classes.
Let us see the below entity relationship for our better understanding.
If you can observe the above diagram the Fronted class had no student enrolled but still then we need to retrieve this class as it exists in the right table.
Now we will write a query for the right outer join. The results will look like below.
For more Queries you can also check our other article Top 4 SQL queries asked in interviews.
Full Outer Join
Full outer join query will the matching and non-matching records from the right table and the matching and non-matching records from the left table.
Non-matching records from both the tables will be considered as null.
In other words, the query will retrieve the matching and non-matching records from table 1 and all the matching and non-matching records from table 2.
Now let us see the use of SQL Joins Full Outer Join with real-time example.
In this example, we would like to know everything about everybody.
The query for the full outer join would be something like below.
In this article we have discussed the topics related to self join in sql, sql join types, natural joins in sql, outer join in sql, my sql joins.
If you still have queries on SQL Joins and would like to know more about this topic you can check here.
Please leave your comments in the comments section so that we will try to enhance the article much better.