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.
1. Inner Join
As described above, the inner join returns records that have matching values in both tables.
Syntax you shall use:
Output
2. Left Join
As described above, left join returns all records from the left table, and the matched records from the right table.
Syntax:
Result
3. Right Join
As described above, right Join returns all records from the right table, and the matched records from the left table.
Syntax:
Result
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:
Output
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.
Top comments (1)
Nice one. If anyone wants to play with joins we’re letting people register for free to create a cloudlet that contains SQL Studio below 😊