SQL Joins Simplified
Hello Folks,
Being a part of the IT world, you probably would have encountered a situation wherein you had to deal with the databases directly or indirectly. It is one of the core subjects of the computer world. Hence, it is of utmost importance to understand the common basics of it that might come in handy in day-to-day corporate tasks. And what better than JOINS to begin with? This blog is all about various joins explained in a simple yet effective manner.
A join is commonly used to combine data distributed across multiple tables in SQL databases. It is easy to understand different types of joins, however, at times it can be challenging to identify which join should be applied to output the required data. So, I will be sharing an example of each different type of join and the way I analyse the end goals to identify which join shall be applied at a particular point of time.
Let’s consider a database that stores the details of the college students like their basic demographic details and the streams they are enrolled into the college. So here we have two tables namely students and streams . As the name suggests, the table students contains the basic demographic details of the students like their names, dob and place of living. The table streams contains the information regarding the streams offered by the college.
Following is the schema and data present in those tables:
Before we proceed ahead, I would like to bring to your attention that the student named Vishal Patel with student id 9 is a new student and has not yet enrolled in any of the courses. Also, among the five different streams that the college offers, no student has yet opted for the Instrumentation & Control stream.
Now let’s assume that we want to obtain the details of all the students along with the streams they are enrolled into (if any).
Then we will have to apply join between the tables students and streams. However, we need to identify here that which join we should apply that provides us with our desired output.
So in order to identify that, we shall relook at our end goal here. We want the details of all the students to be displayed irrespective of the fact whether they have enrolled in a stream or not. If they have enrolled in any of the stream, then the stream details also needs to be displayed or else just display the details of the students and keep the stream details to be null.
So here we need to apply a Left Join. Generally, when we need to retrieve all the entries from table1 and fetch the matching entries from table2 then we need table1 left join table2 on some common column (here stream_id).
Here in the result of left join, we can see the student Vishal Patel is appearing in the output despite the fact that he hasn’t finalized any stream yet.
Get Mihirsheth’s stories in your inbox
Join Medium for free to get updates from this writer.
Let’s now aim to obtain the details of only those students that have enrolled in one of the streams offered by the college.
So here we have two tables table1 and table2, wherein we want to display the data for table1 which must have a matching entry for the common column in table2. Inner join is the join which we are looking for in this case.
In the above output that is obtained after applying the inner join, we can clearly see that the student Vishal Patel isn’t appearing. The reason being the row for this student in the students table doesn’t have a single matching entry with the streams table for the common column stream_id.
Now, let’s assume that we want details of the students who must have enrolled in one of the streams along with the details of all the streams that are offered by the college.
So here basically we need matching entries of table1 with table2 along with the entries from table2 that aren’t included in any of the match. Right join works rightly for this case.
In the above result of right join, we can see the records from students which had a match with the streams table on the common column. However, there also appears an additional entry for Instrumentation & Control stream which isn’t selected by any of the students yet.
Finally, let’s consider a situation wherein we want the details of all the college students whether they have enrolled in a stream or not along with the details of all the streams offered by the college irrespective of the fact whether any student has choosen that stream or not.
So, here for two tables table1 and table2, we want to find all the matching entries along with the non-matching ones from both table1 and table2 as well.
So Outer Join is the preferred choice here. Please note that mysql doesn’t provide the facility to apply outer join directly as some other SQL databases do. However, you can apply the outer join in the following way in mysql.
Hope this article on Joins helps the beginners to get familiar with the concept and amateur ones to have a good revision
