SQL Joins

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

In this topic, we will be discussing

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

Will write another article on some interview questions on joins.

Let’s begin,

What are Joins

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 related to 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

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

I have a Product table having Id which acts as primary key, a Product and Category_id which acts as foreign key as its columns, and a Category table having 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

SQL Joins

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 names and category names from product and category tables respectively on the basis of their Category_id. If the category id is present in both the tables then only that tuple(row) will be picked otherwise not.

SQL Joins

Syntax

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

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

Left Join

 SQL Joins

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

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

SQL Joins

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

Right Join

SQL Joins

Right Join is basically the opposite of Left join.

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

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

SQL Joins

Full Join

SQL Joins

Full Join returns data from both the table either match found or not. In case of a match not found then, Null is returned.

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

SQL Joins

Cross Join

SQL Joins

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 each and every data

SQL Joins

That’s all about joins.

Summary

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