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

OrderIDCustomerIDOrderDate
10101CU0012021-09-18
10102CU0022021-09-18
10103CU0012021-09-20
10104CU0042021-09-20

table2: Customers

CustomerIDCustomerNameAddressCity
CU001Nam Hà16 Lý Thường KiệtĐà Nẵng
CU002Văn Thanh10 Hùng VươngHà Nội
CU003Cẩm Tú14 An ThượngHải Phòng

1. INNER JOIN

–> Connection based on common data of 2 tables

Syntax

Example

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

OrderIDCustomerNameAddressCity
10101Nam Hà16 Lý Thường KiệtĐà Nẵng
10102Văn Thanh10 Hùng VươngHà Nội
10103Nam Hà16 Lý Thường KiệtĐà Nẵng

2. LEFT JOIN

Syntax

Example

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

OrderIDCustomerNameAddressCity
10101Nam Hà16 Lý Thường KiệtĐà Nẵng
10102Văn Thanh10 Hùng VươngHà Nội
10103Nam Hà16 Lý Thường KiệtĐà Nẵng
10104nullnullnull

3. RIGHT JOIN

Syntax

Note: RIGHT JOIN or RIGHT OUTER JOIN are fine.

Example

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

OrderIDCustomerNameAddressCity
10101Nam Hà16 Lý Thường KiệtĐà Nẵng
10102Văn Thanh10 Hùng VươngHà Nội
10103Nam Hà16 Lý Thường KiệtĐà Nẵng
nullCẩm Tú14 An ThượngHải Phòng

4. FULL JOIN

Syntax

Note: FULL JOIN or FULL OUTER JOIN are fine.

Example

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

OrderIDCustomerNameAddressCity
10101Nam Hà16 Lý Thường KiệtĐà Nẵng
10102Văn Thanh10 Hùng VươngHà Nội
10103Nam Hà16 Lý Thường KiệtĐà Nẵng
10104nullnullnull
nullCẩm Tú14 An ThượngHả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

Example

The [Orders] table is immediately combined with itself, to find the OrderIDs with the same OrderDate

Result

Order01Order02OrderDate
10101101022021-09-18
10103101042021-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

Share the news now