Explaining SQL Joins with MySQL Implementation

Even though there are a lot of resources out there that explain Joins in SQL, there still happens to be a lot of confusion with this topic.

So, Let’s start with the different types of JOINS that MySQL has and what they are.

• INNER JOIN
• OUTER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN

Joins, as the name suggests, is a technique used to join the columns of multiple tables together. Understanding each type of join can be better done with the help of examples.

Let us start with the understanding of the concepts first. The code for MySQL will be available at the end of the blog. If you are already familiar with the theory, you can scroll down and proceed to the syntax.

INNER JOIN

Inner Join can be thought of as the intersection of the rows from 2 tables based on the common columns in both the tables.

For Example, If we have a student table and the marks table as follows

fig 1.1: student table

fig 1.2: marks table

As we can see, the common column between both these tables is the student id ( id in first table and stud_id in second table ) and we can also see that, the marks table does not contain the marks of “Athulan” and “Sriram”. marks table also has the marks of the student who is not registered in the student table.

Inner Join requires us to specify the column names based on which we JOIN the 2 tables. From the first table , we use id and the second table, we use stud_id ( Both of which represents the id of the students ).

students.id = {1,2,3,4}; marks.stud_id = {1,3,5}.

In Inner Join, we consider the intersection of these 2 sets. i.e, { 1,3 }.
So, from the first table, rows where id = 1 and id = 3 are taken and these rows are respectively merged with their corresponding second table rows.
The corresponding second table rows are the rows where stud_id = 1 and stud_id = 3.

So, The result would be this:

The student table does not contain a student with id=5. The marks table does not contain the students with id=2,4. So, the only students that are common in both the tables are students with id= 1 and 3. Hence, The INNER JOIN will only take in these 2 students(rows).

Left Join and Right Join

Left Join and Right Join are the 2 sides of the same coin. So, understanding left join will be more than enough to understand the right join.

When we join 2 tables, There are 2 entities : The left table and the right table. What left join basically does is that, It fills in the values for the left join first. Then, It fills the corresponding columns of the right table if those values exists for the corresponding row, else that placeholder will be filled with null.

An example would be able to simplify this. Let’s consider the same students and marks table from fig1.1 and fig1.2. On performing Left Join with the students table as the left table and the marks table as the right table, The result would be fig 2.1

fig 2.1

In Inner Join, we left our some students because their mark details were not available. But, In Left Join, we first fill all the rows based on the left table. Then, If marks for the student is available from the marks table, We fill the corresponding marks. If marks for a student is not available, we fill that placeholder with null.

One more thing to notice is that marks for a student with id number 5 is available in the marks table. But, this result was not shown in students LEFT JOIN marks, because the details of that student does not exist in the students table and since this is a left join, we give more preference to the details in the left table ( students table ).

Right Join is similar to Left Join But, more preference would be given to the details of the right table. The Students Right JOIN marks would look like this:

fig 2.2

The Columns of the right table would first be entirely filled and then, if the corresponding rows for the columns of the left table exists, they would be filled else null will take their place.

Outer Join

If Inner join is the intersection of the tables, Outer join can be thought of as the union of the tables. Now that we studied Left and Right Joins, Understanding Outer Join is going to be a little easier.
Let’s take the same example again

fig 3.1

So, We do not have a student with id 5 and we do not have the marks for student no 2 and 4.

On doing an Outer Join based on student id, The resultant table would include all the rows from both the table, Even if the corresponding value from the other table does not exist. Unlike Inner Join, where we leave out uncommon rows, we’ll include all the rows here. If the corresponding row from the other table does exists, that value will be filled else null will take it’s place.
The result is:

fig 3.2: Outer Join

Cross Join

Unlike the other Joins, Cross Join is a special one. It does not even require a common column to merge to tables.

Let’s consider a different example for Cross Join.

fig 4.1

Cross Join is basically a Cartesian Product. On doing Cross Join between the above 2 tables, we are basically creating 4 separate rows for each student with each row containing a different sports name. The result is:

Note: A Cross Join will result in a table having number of rows equal to the (number of rows in the left table * the number of rows in the right table).

In the above example, Each and every student will be associated with each and every sport and each and every sport is associated with each and every student.

Code

The code will only contain the join part as the blog post assumes the reader is already familiar with the basics of MySQL

Inner Join

The s and m can be thought of as the alias names to the student and marks table. s.name will access the name column from the student table.

SELECT s.name, m.mark FROM students s INNER JOIN marks m ON s.id = m.stud_id;

Left Join and Right Join

/* Left JOIN */
SELECT * FROM students s LEFT JOIN marks m ON s.id=m.stud_id;
/* Right Join */
SELECT * FROM students s RIGHT JOIN marks m ON s.id=m.stud_id;

Outer Join

MySQL does not directly support OUTER JOIN. But, we can use UNION to hack our way to an Outer Join

SELECT * FROM students s LEFT JOIN marks m ON (s.id = m.stud_id) 
UNION 
SELECT * FROM students s RIGHT JOIN marks m ON (s.id = m.stud_id);

Cross Join

SELECT * FROM students s CROSS JOIN sports sp;

Conclusion

That’s all for the MySQL Joins and they are a very powerful tool which come in handy in many situations.
There are a lot of times when you can use Joins to simplify the tasks instead of writing several nested sub-queries and making the code unreadable and complex.

Thank You