SQL JOINS
Tags: beginners, codenewbie, 100daysofcode, sql
Published: 2022-07-15 10:27:26

## SQL Joins

Say for example you have multiple tables. One table, for example, Products, is missing the Supplier Column. But when you check a different table, for example Supplier, you find it has the Supplier Column you need. So how do you get the Supplier Column to the Product table, whilst ensuring all details in the rows are accurate and matching?

This is what we shall cover today - SQL JOINS. Let’s get started:-

 - **What is an SQL Join statement?**  
 - **Types of SQL Joins**




**What is an SQL Join statement?**

SQL Join statement is used to combine data or rows from two or more tables, based on a related column between them.

**Sample Data:-**

**Table 1 – Products Table**

Product_ID|Product_Name|Customer_ID|
|------------|-------------------|------------------------|
|100|Rice  |3|
|101|Soap|5|
|102|Bread|2|
|103|Milk|1|
|104|Water|4|

**Table 2 – Supplier Table**

Supplier_ID|Product_Name|
|------------------|------------|
|A01|Bread|
|A02|Rice|
|A03|Milk|
|A04|    |
|A05|Soap|

**Note that** for the Supplier table we have a **NULL** on the Product_Name for the Supplier_ID A04.

**Types of Joins**

 - **Inner Join** - Returns records that have matching values in both
   tables.
 - **Left Join** - Returns all records from the left table, and the matched
   records from the right table.
 - **Right Join** - Returns all records from the right table, and the
   matched records from the left table.
 - **Full Join** - Returns all records when there is a match in either left
   or right table.

![image](https://user-images.githubusercontent.com/40733583/179205516-59860329-de49-43f3-b9cf-7a52315038c5.png) 

**1. Inner Join**

As described above, the inner join returns records that have matching values in both tables.

**Syntax you shall use:**
![image](https://user-images.githubusercontent.com/40733583/179201485-48062742-6780-43b8-9f85-2e23c7b66906.png)

**Output**
![image](https://user-images.githubusercontent.com/40733583/179201957-40b4cfd6-294d-49b1-a360-01e4d2ba9719.png)

**2. Left Join**

As described above, left join returns all records from the left table, and the matched records from the right table.

**Syntax:**
![image](https://user-images.githubusercontent.com/40733583/179202105-542c78ba-d785-44ae-97cd-9afab821ad3b.png)

**Result**
![image](https://user-images.githubusercontent.com/40733583/179202174-25b8cbb3-2530-48a5-8901-2937126b1d37.png)

**3. Right Join**

As described above, right Join returns all records from the right table, and the matched records from the left table.

**Syntax:**
![image](https://user-images.githubusercontent.com/40733583/179202276-5e15128d-625f-4caa-be84-96bce1677a05.png)

**Result**
![image](https://user-images.githubusercontent.com/40733583/179202340-ef6c7af4-e48e-4dea-a55d-f13aadfa6572.png)

**4. Full Join**

As described above, the full join returns all records when there is a match in either left or right table. MySQL does not support Full Join keywords, hence we shall use UNION to combine the Right Join and Left Join so as to create a Full Join.

**Syntax:**
![image](https://user-images.githubusercontent.com/40733583/179202411-44c4761d-b098-4937-9196-9e891c22dfdf.png)

**Output**
![image](https://user-images.githubusercontent.com/40733583/179202493-3b68544c-58b1-4a43-8635-07080daa44ed.png)


**To Note:**

As seen in the Full Join, the Supplier_ID **‘A04’** did not have a Product_Name, hence the details are marked as NULL even after performing Joins.