SQL Advance – Joins
- Tram Ho
Querying data on a table is quite simple and easy to use in SQL. But with 2 or more tables, the query becomes more difficult when having to join many tables together. Must find the appropriate conditions and ways to query the data you want refer to.
In this article, I will introduce how to use the Joins statement in SQL. Depends on the context and purpose of use. Hope you will choose the appropriate usage.
The data I give for example has 2 tables as follows
table1: Orders
OrderID | CustomerID | OrderDate |
---|---|---|
10101 | CU001 | 2021-09-18 |
10102 | CU002 | 2021-09-18 |
10103 | CU001 | 2021-09-20 |
10104 | CU004 | 2021-09-20 |
table2: Customers
CustomerID | CustomerName | Address | City |
---|---|---|---|
CU001 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
CU002 | Văn Thanh | 10 Hùng Vương | Hà Nội |
CU003 | Cẩm Tú | 14 An Thượng | Hải Phòng |
1. INNER JOIN
–> Connection based on common data of 2 tables
Syntax
1 2 3 4 5 | SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; |
Example
1 2 3 4 | SELECT Orders.OrderID, Customers.CustomerName,Customers.Address,Customers.City FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
Compare the [Orders] and [Customers] tables.
- For each row in the Orders table, find the corresponding rows in the Customers table.
- If a row that matches the condition with the same CustomerID is found, the line will be added to the corresponding result.
- If multiple matching lines are found, multiple result lines will be displayed
Result
OrderID | CustomerName | Address | City |
---|---|---|---|
10101 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
10102 | Văn Thanh | 10 Hùng Vương | Hà Nội |
10103 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
2. LEFT JOIN
Syntax
1 2 3 4 5 | SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; |
Example
1 2 3 4 5 | SELECT Orders.OrderID, Customers.CustomerName,Customers.Address,Customers.City FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
Compare the [Orders] and [Customers] tables.
- The way to do the same with INNER JOIN, but with rows in the Orders table, if the corresponding row cannot be found in Customers, it will still be displayed.
Result
OrderID | CustomerName | Address | City |
---|---|---|---|
10101 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
10102 | Văn Thanh | 10 Hùng Vương | Hà Nội |
10103 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
10104 | null | null | null |
3. RIGHT JOIN
Syntax
1 2 3 4 5 | SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; |
Note: RIGHT JOIN or RIGHT OUTER JOIN are fine.
Example
1 2 3 4 5 | SELECT Orders.OrderID, Customers.CustomerName,Customers.Address,Customers.City FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
Compare the [Orders] and [Customers] tables.
- The same way with INNER JOIN, but the rows in the Customers table that do not correspond to the Orders table are still displayed. Now the corresponding values in the Orders table will display NULL
Result
OrderID | CustomerName | Address | City |
---|---|---|---|
10101 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
10102 | Văn Thanh | 10 Hùng Vương | Hà Nội |
10103 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
null | Cẩm Tú | 14 An Thượng | Hải Phòng |
4. FULL JOIN
Syntax
1 2 3 4 5 | SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name |
Note: FULL JOIN or FULL OUTER JOIN are fine.
Example
1 2 3 4 5 | SELECT Orders.OrderID, Customers.CustomerName,Customers.Address,Customers.City FROM Orders FULL JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
Compare the [Orders] and [Customers] tables.
- The same way with INNER JOIN, but the rows in the Customers table that do not correspond to the Orders table are still displayed. Now the corresponding values in the Orders table will display NULL
- Rows in the Orders table that do not correspond to the Customers table are still displayed. Now the corresponding values in the Customers table will display NULL
Result
OrderID | CustomerName | Address | City |
---|---|---|---|
10101 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
10102 | Văn Thanh | 10 Hùng Vương | Hà Nội |
10103 | Nam Hà | 16 Lý Thường Kiệt | Đà Nẵng |
10104 | null | null | null |
null | Cẩm Tú | 14 An Thượng | Hải Phòng |
5. SELF JOIN
–> In addition to joining from 2 tables, we can join itself to query and find out what data to use.
Syntax
1 2 3 4 | SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; |
Example
1 2 3 4 5 | SELECT A.OrderID as Order01, B.OrderID as Order02, A.OrderDate FROM Orders A, Orders B WHERE A.OrderID <> B.OrderID AND A.OrderDate = B.OrderDate |
The [Orders] table is immediately combined with itself, to find the OrderIDs with the same OrderDate
Result
Order01 | Order02 | OrderDate |
---|---|---|
10101 | 10102 | 2021-09-18 |
10103 | 10104 | 2021-09-20 |
Different Types of SQL JOINs
Here is an overview so you can easily compare the differences between Joins types. Hope the article will be helpful to you.
Articles with references https://www.w3schools.com