Introduction to Types of Joins in SQL
Joins in SQL can be of four different types, subjected to the outcome expected by combining records from two or more tables by making use of the common columns from tables involved in the Join function. Inner Join is used to get records with same values In both tables, Left Join is used to get all records from Left side table and only matching values from right table and Right Join is used to get all records from Right table and only matching records in left table. finally Full Join to get all the records from both tables irrespective of matching records.
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 conditionTypes 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 condition2. 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 condition3. 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 condition4. 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 conditionExamples 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_IdOutput:
| 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_IdOutput:
| 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_IdOutput:
| 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_IdOutput:
| 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
- 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, and DELETE.
- Joins also get along with different clauses like – GROUP BY, SUB QUERIES, WHERE, AGGREGATE FUNCTIONS, etc.
Recommended Articles
This has been a guide to Types of Joins in SQL. Here we discuss the basic concept, different types of Joins in SQL, and its advantages along with examples. You can also go through our other suggested articles to learn more –
