Understanding SQL JOINS

JOIN, one of the key components of SQL, without which collecting data from multiple tables, in a single query, in a structured format would nearly be impossible.

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. - w3schools

Simple right? Let’s start with an example, suppose you got 2 tables fees and students

fees table:

students table:

Notice that both tables have studentId as a common column. Now we have chance to create the type of relationship (JOIN) we want between 2 tables. The blog post assumes here on out that you know how to query data using WHERE clause.

Be aware that same column name doesn’t always mean they are the ones to create a JOIN on. In most cases, students table would have id as column name for studentId column. Clearly, column name has nothing to do with creating relationship. You must know that yeah that studentId from fees table is id/studentId column in students table.

We’re going to create an INNER JOIN (I’ll explain what it means below, so keep reading) on studentId column and let’s say we query for feeId - 145237.

The SQL statment would look like,

SELECT fees.FeeId, students.name, fees.paidDate 
FROM fees INNER JOIN students 
ON students.studentId=fees.studentId 
WHERE feeId='145237';

There may be a question arrising in your mind. Is selecting columns necessary? No, Instead of this selection if I had used *, the result would have 5 column with 2 studentId columns. Using * returns all columns of both tables.

The query result looks like this,

So, now you’ve got the basic idea how JOIN works, time to dive deep. I will be using only 2 tables for explaining everything but I’m pretty sure, same concepts are applicable to any number of tables.

There are 5 basic JOINs,

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
5. CROSS JOIN

Inner Join

The INNER JOIN selects record that have matching values in both tables. The venn diagram below shows it in the most simplest form.

The syntax for creating an Inner Join would look like this,

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Now you already know, what values to subtitute as we have already used Inner Join in the example of students and fees. Lets see what happens if we remove the WHERE clause, simple. It will return all the rows with matching column (studentId) values.

We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.

Left Join

Consider the following venn diagram -

It is clear that LEFT JOIN selects all the records from table 1 (the one that we are creating a join on) and matching records from table 2.

Now table 1 and table 2 are confusing terms, How I mentally remember this is, LEFT JOIN will select all the records of the table that we specify after the FROM clause and matching records from the other table.

The query for Left join would look like this,

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Now if you subtitute appropriate values in this query and make it run for our students and fees table, the result would look something like this.

Right Join

You guessed it right! RIGHT JOIN is quite the opposite of LEFT JOIN. RIGHT JOIN selects all the records from table 2 and matching records from table 1.

If you find table 1/2 a confusing terminology then I’d suggest use the way, how I try to remember this. I would say select only matching records from the table specified after FROM clause and all the records from the joined table.

Here is how RIGHT JOINs venn diagram would look,

The query for Right Join would have this structure,

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Now if we substitute appropriate values like we did for LEFT JOIN for students and fees table, the result would be this.

Full Join

The FULL JOIN (also referred to as FULL OUTER JOIN) returns all the records whether they are matched or not.

The query for full join would look like this,

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Now if we substitute appropriate values like we did for other queries the result would look something like this,

Please note that Mysql doesn’t support FULL JOIN but there are ways we can simulate it. Now I can write a whole article on it but I’d rather suggest to read this blog post.

Cross Join

The CROSS JOIN returns all the records from both tables. Putting it simply, cross join would result in joining every row of one table to every row of other table. To make sense, let’s use it with students and fees table.

The query would look like this,

SELECT *
FROM fees
CROSS JOIN students

Running this query would result in following data,

Probably not what you expected, Right?

CROSS JOIN can potentially return very large result-sets! If you add a WHERE clause with CROSS JOIN, it would return same result as an INNER JOIN query.

Conclusion

JOINS are awesome for collecting data from multiple tables, all at once. They eases your life as a developer and would also make you look super smart with all the JOINs in a single query (I mean, who worries about the next guy touching this code).

We saw that there 5 basic Joins, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN and CROSS JOIN and how to implement them.

Good luck!