JOINS: A powerful skill to have in your kitty!

JOINS: A powerful skill to have in your kitty!

Inner Join Vs Outer Join:
Before we understand different types of joins, I think it’s important to understand the purpose behind using it. As we know, JOINs is one the most powerful features of SQL used for gathering and manipulating data from across different tables. Without this feature, we would have to store all the data belonging to different elements in one table, or we would have to save the same data in different tables. This becomes an overhead because every time we need a new piece of information, we are redesigning and rebuilding our databases to provide that information. Hence, joins to the rescue!

Imagine, we have data in different tables and we want to retrieve a piece of information that is present in both the tables. Joins enable us to combine information that is present in different tables, thus allowing us to normalize the database logically such that we have smaller, specific tables that are easier to maintain than larger tables. The smaller tables are designed such that one can establish a relation from one tablemto another using a common or related column. Hence, the term relational databases. E.g. StudentProfile and StudentMarks could be twotables but they would be related by studentId column.

Now let’s move on to the types of joins. We have Inner Join, Outer Join, Cross Join and Self Join. For the purposes of this article, I’ll only be explaining inner and outer join.

Let’s consider two tables: Users and Tweets
Users stores the user information and Tweets stores the tweet information of each user.

INNER JOIN:
Inner join retrieves matching rows from both the tables based on a common column between these tables. If there is a column called ‘profile_id’ in both the tables, doing an inner join will retrieve rows matching the profile_id from both tables. Mathematically speaking, inner join is the intersection of the two tables.

You would write an inner join statement the following way:
select users.*,
tweets.*
from users
inner join tweets
ON users.profile_id = tweets.profile_id

OUTER JOIN:
Outer join is different from inner join in the way that it returns unmatched rows from one or more tables. There are few types of outer joins:
1. Left Join: Apart from returning the matching rows from one or more tables, it also returns unmatched rows from the left table.
2. Right Join: Apart from returning the matching rows from one or more tables, it also returns unmatched rows from the right table.
3. Full Outer Join: Apart from returning the matching rows from one or more tables, it also returns unmatched rows from all tables.

You would write an left outer join query the following way:
select users.*,
tweets.*
from users
left join tweets
ON users.profile_id = tweets.profile_id

The above query will return all user information as ‘users’ is the left table, but also matching rows/ tweets from the right table ‘tweets’.

To perform right/ full join, replace ‘left’ with the type of join that is desired. We also need to keep to mind the tables that we want to retrieve information from.

By: Shagufta Pathan

 

Leave a Reply

Your email address will not be published. Required fields are marked *