Introduction to Types of Joins in SQL
A Join clause in SQL serves the purpose of combining rows from two or more tables. The process of combining rows depends on a column that is related to them. The four different types of joins in SQL are named as an inner join, left join, right join and full join. Before we can really begin to use any type of join, we need an RDBMS or a Relational Database Management System where we will load the data. One of the very easy ways to understand the joins is by the use of the Venn diagram. Using a Venn diagram makes it easy to gauge all possible and logical relations between different sets of data. Let us understand this one by one using the Venn Diagram. Later we will understand the difference between these using some practical examples. Let us suppose that there are two data sets in our database stored in the form of Table One and Table Two. There is some relation between the two tables which is specified in the form of a primary key and a foreign key concept. If you join two tables having some sort of relationship the Venn diagram representation of the scenario will be something like,
The amount of this overlap will determine the extent of similarity between the two tables i.e, Table A and Table B. This means that the number of records from table one, that are matching with the records from table two is represented by the overlap section. This is one subset of data. We get four different types of joins based on the subset of data we are picking from the two tables.
Syntax of Join:
SELECT column-names FROM table-name1 JOIN table-name2 ON column-name1 = column-name2 WHERE condition
Types of Joins in SQL
- Inner Join
- Left Join
- Right Join
- Full Join
Below we explain different types of Joins in Detailed.
1. Inner Join
In an inner join, we only select the data which is common in both the tables. (ie, part 3 here) In order to make it more precise, all the records from both the tables matching up the condition mentioned with the join are picked in this join.
Syntax of Inner Join:
SELECT column-names FROM table-name1 INNER JOIN table-name2 ON column-name1 = column-name2 WHERE condition
2. Left Join
In left join, we select all the data from the left table and from the right table only select the data set which matches up with the condition mentioned with the join (here area 1+3)
Syntax of Left Join:
SELECT column-names FROM table-name1 LEFT JOIN table-name2 ON column-name1 = column-name2 WHERE condition
3. Right Join
In right join, we select all the data from the right table and from the left table only select the data set which matches up with the condition mentioned with the join (here 3+2)
Syntax of Right Join:
SELECT column-names FROM table-name1 RIGHT JOIN table-name2 ON column-name1 = column-name2 WHERE condition
4. Full Join
In full join, all the records form both the tables are merged and selected irrespective of the condition mentioned with the join having met or not. (here 1+2+3)
Syntax of Full Join:
SELECT column-names FROM table-name1 FULL JOIN table-name2 ON column-name1 = column-name2 WHERE condition
Examples of Joins in SQL
Consider the two table given below:
1. Example of an ORDER_DETAILS
The table order contains the detail of the order placed by the customer such as the order id, the number of products ordered, the amount of the order, the id of the customer who placed the order and the date on which the order was placed. Such kind of table can be used by any online website for storing the details of the order.
ORDER_DETAILS Table:
| Order_ID | No_of_Items | Order_Amount | Customer_Id | Order_Date |
| 123 | 3 | 5500 | P_1 | 01/20/2019 |
| 234 | 2 | 6500 | P_12 | 02/10/2019 |
| 345 | 1 | 10000 | P_13 | 05/27/2019 |
| 456 | 4 | 4000 | P_14 | 11/07/2019 |
| 567 | 2 | 20656 | P_1 | 12/15/2019 |
| 678 | 3 | 15000 | P_11 | 10/27/2019 |
2. Example of CUSTOMER_DETAILS
Now let us take another table in which the details of the customer will be stored so as to be able to deliver the order to their respective addresses. The customer table will, therefore, have the details of the customer such as customer id (cust_id) which will be unique for each customer. Now the first name and the last name are stored in the field called – Cust_First_Name and Cust_Last_Name. The other fields will store the information such as the email id, mobile number of the customer along with the address, Pincode, city, and state. Thus, we can see that our Customer table will look something like –
CUSTOMER_DETAILS Table :
| Cust_Id | Cust_First_Name | Cust_Last_Name | Pin Code | Address | Cust_Mobile | City | State | Cust_email |
| P_50 | Alice | Peter | 111111 | 330 xyz street | 123 | Bangalore | KA | alice_p@gmail.com |
| P_12 | James | Dsouza | 155511 | 420 abc colony | 234 | Hyderabad | AP | jame_d@gmail.com |
| P_15 | Harry | Potter | 123456 | 551 mg road | 444 | Noida | Delhi | hpotter@outlook.com |
| P_40 | Miley | Parker | 111121 | 11 feet road | 224 | Bangalore | KA | park01Miley@gmail.com |
| P_10 | Herman | Bush | 123423 | 34th thanco Street | 432 | Delhi | Delhi | bushh@yahoo.com |
| P_18 | Dan | Brown | 134523 | 50th independent road | 145 | Gurugram | Haryana | dan_brown@gmail.com |
| P_20 | James | Russel | 111111 | 101 mg road | 678 | Bangalore | KA | russelJ@gmail.com |
| P_1 | Miley | Madison | 100011 | 45th kaverappa layout | 987 | Chennai | TN | mmson@yahoo.com |
Now using this example, let us understand the functionality of the joins. We can see that the relationship between the two tables i.e, the ORDER_DETAILS table and the CUSTOMER_DETAILS table is established by the key which has the value of the customer id i.e, Customer_Id which is a Primary Key in the CUSTOMER_DETAILS table and a foreign key in the ORDER_DETAILS table.
Few important points to note here before we proceed are :
- Not all the customers in our CUSTOMER_DETAILS table has placed an order request.
- Not all the order request that we have in our ORDER_DETAILS table has a customer_id which is present in our CUSTOMER_DETAILS table which means that for some orders there is no detail of the customer.
3. Inner Join
Inner Join will give you only those records for which the condition is fulfilled.
Query:
select Cust_First_Name, Cust_email, No_of_Items, Order_Amount, Order_Date
from CUSTOMER_DETAILS cd
inner join ORDER_DETAILS od
on cd.Cust_Id = od.Customer_Id
Output:
| Cust_First_Name | Cust_email | No_of_Items | Order_Amount | Order_Date |
| Miley | mmson@yahoo.com | 3 | 5500 | 01/20/2019 |
| James | jame_d@gmail.com | 2 | 6500 | 02/10/2019 |
| Miley | mmson@yahoo.com | 2 | 20656 | 12/15/2019 |
4. Left Join
Left Join will give you all the records from the left table i.e, the CUSTOMER_DETAILS table. If there are no orders placed by the customer it will return a null value for the columns in the ORDER_DETAILS table.
Query:
select Cust_First_Name, Cust_email, No_of_Items, Order_Amount, Order_Date
from CUSTOMER_DETAILS cd
left join ORDER_DETAILS od
on cd.Cust_Id = od.Customer_Id
Output:
| Cust_First_Name | Cust_email | No_of_Items | Order_Amount | Order_Date |
| Alice | alice_p@gmail.com | NULL | NULL | NULL |
| James | jame_d@gmail.com | 2 | 6500 | 02/10/2019 |
| Harry | hpotter@outlook.com | NULL | NULL | NULL |
| Miley | park01Miley@gmail.com | NULL | NULL | NULL |
| Herman | bushh@yahoo.com | NULL | NULL | NULL |
| Dan | dan_brown@gmail.com | NULL | NULL | NULL |
| James | russelJ@gmail.com | NULL | NULL | NULL |
| Miley | mmson@yahoo.com | 3 | 5500 | 01/20/2019 |
| Miley | mmson@yahoo.com | 2 | 20656 | 12/15/2019 |
5. Right Join
Right, Join will give you all the records from the right table i.e, the ORDER_DETAILS table. If there are no customer records found for the order it will return a null value for the columns in the CUSTOMER_DETAILS table.
Query:
select Cust_First_Name, Cust_email, No_of_Items, Order_Amount, Order_Date
from CUSTOMER_DETAILS cd
right join ORDER_DETAILS od
on cd.Cust_Id = od.Customer_Id
Output:
| Cust_First_Name | Cust_email | No_of_Items | Order_Amount | Order_Date |
| Miley | mmson@yahoo.com | 3 | 5500 | 01/20/2019 |
| James | jame_d@gmail.com | 2 | 6500 | 02/10/2019 |
| NULL | NULL | 1 | 10000 | 05/27/2019 |
| NULL | NULL | 4 | 4000 | 11/07/2019 |
| Miley | mmson@yahoo.com | 2 | 20656 | 12/15/2019 |
| NULL | NULL | 3 | 15000 | 10/27/2019 |
6. Full Join
Full Join will give you all the records specified from both the tables.
Query:
select Cust_First_Name, Cust_email, No_of_Items, Order_Amount, Order_Date
from CUSTOMER_DETAILS cd
full join ORDER_DETAILS od
on cd.Cust_Id = od.Customer_Id
4.5 (1,880 ratings)
View Course
Output:
| Cust_First_Name | Cust_email | No_of_Items | Order_Amount | Order_Date |
| Alice | alice_p@gmail.com | NULL | NULL | NULL |
| James | jame_d@gmail.com | 2 | 6500 | 02/10/2019 |
| Harry | hpotter@outlook.com | NULL | NULL | NULL |
| Miley | park01Miley@gmail.com | NULL | NULL | NULL |
| Herman | bushh@yahoo.com | NULL | NULL | NULL |
| Dan | dan_brown@gmail.com | NULL | NULL | NULL |
| James | russelJ@gmail.com | NULL | NULL | NULL |
| Miley | mmson@yahoo.com | 3 | 5500 | 01/20/2019 |
| Miley | mmson@yahoo.com | 2 | 20656 | 12/15/2019 |
| NULL | NULL | 1 | 10000 | 05/27/2019 |
| NULL | NULL | 4 | 4000 | 11/07/2019 |
| NULL | NULL | 3 | 15000 | 10/27/2019 |
Advantages of Joins in SQL
- Faster execution which means faster retrieval of desired columns.
- Optimized, Readable and Understandable
- Increase in performance.
Conclusion
As seen, we use JOINs to append & get the fields from different tables.
- Inner Join fetches records where the given condition is fulfilled.
- Left Join will give you all the rows from the left table, even when the given condition does not match.
- Right Join will give you all the rows from the left table, even when the given condition does not match.
- Full Join returns all the rows when there is a match in one of the tables.
- Join queries can be used with commands like – SELECT, INSERT, UPDATE, DELETE.
- Joins also get along with different clauses like – GROUP BY, SUB QUERIES, WHERE, AGGREGATE FUNCTIONS, etc.
Recommended Article
This has been a guide to Types of Joins in SQL. Here we discuss the different types of Joins in SQL and its Advantages along with Examples. You can also go through our other suggested articles to learn more –
