Types of Joins in SQL

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 condition

Types of Joins in SQL

1. Inner Join
2. Left Join
3. Right Join
4. 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

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

1. Faster execution which means faster retrieval of desired columns.
2. Optimized, Readable, and Understandable
3. 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 –

1. SQL Management Tools
2. Database in SQL​ 
3. Wildcard in SQL
4. Joins in MySQL