SQL Joins

Introduction

SQL Joins are considered the most difficult topic in SQL. My motive in this article is to explain things in such a way that is understandable.

In this topic, we will be discussing

1. What are Joins?
2. How many types of join do we have?
3. Understanding of every joins with examples.
4.  Summary

I will write another article on some interview questions on joins.

Let's begin,

What are Joins in SQL?

In simple words, Join = Combine.

Join is used to retrieve data from two or more tables based on some related columns. In simple words, these tables are always somehow associated with each other.

How many types of join do we have?

There are mainly five types of joins. They are,

1. Inner Join
2. Left Join
3. Right Join
4. Full Join
5. Cross Join

To learn Joins properly, I will be here considering some tables, and then we will learn about joins from the tables below.

I have a Product table with Id which acts as the primary key, a Product and Category_id, which acts as foreign key as its columns, and a Category table with Id, which is the primary key, and Category as its columns.

Category

Id	Category
1	Electronics
2	Clothes
3	Furniture
4	Grocery

Product

Id	Product	Category_id
1	Peanut Butter	4
2	Kelloggs Muesli	4
3	LED	1
4	Dining table	3
5	Shoes	5

Inner Join in SQL

After visualizing the figure, we can say that the inner join will return those data from the tables for which only matched values will be found.

For example, you want to get a list of product and category names from product and category tables, respectively, based on their Category_id. If the category id is present in both tables, then only that tuple(row) will be picked; otherwise, it will not.

Syntax

SELECT table1.column, table1.column, table2.column, table2.column
From table1
Inner Join table2 ON table1.column = table2.column

We can use Join as well in place of Inner Join.

Left Join in SQL

 

Left Join will return all the rows from the left table even if no matching row is found from the right table. The NULL will be returned where no matching row is found on the right table.

For example, you want to get all your categories and the product associated with your categories.

NULL is returned for all the non-matching rows in the Product table.

Right Join in SQL

Right Join is the opposite of Left join.

Right, Join will return all the rows from the right table even if no matching row is found from the left table. The NULL will be returned where no matching row is found on the left table.

For example, You want to get all your products list and the categories associated with your products.

Full Join in SQL

Full Join returns data from both tables, whether a match is found. In case of a match is not found, then Null is returned.

For example, We want to see if our tables have all the required customer data, like proper category tagging with the products. Then, in that case, we will perform the full join to check where we have any Null value so we can make an entry for that.

Cross Join in SQL

Cross Join is where each row of the first table is combined with every other row of the second table. This is also known as Cartesian Join.

This is used when you have a grid, and you have to populate that grid with every data.

That's all about joins.

Summary

• JOINS allows combining data from more than one table into a single result, reducing the server overhead.
• The major JOIN types include Inner, Left, Right, Full, and Cross JOINS.
• INNER JOINS only returns rows that meet the matched condition only.
• Full Join is a combination of left join and right join.
• We can also use the "USING" clause instead of the "On" clause with join.