Have you ever had to scratch your head when your boss asked for a summary report from dozens of different data tables? I did, and that's when I realized SQL JOIN is the real "savior". Forget the dry definitions in textbooks, this article is my real story (Pham Hai) sharing about SQL JOIN types of explanations with examples that are most detailed and easy to understand. I will show you exactly when to use INNER and when LEFT is the true love so you will never get wrong data again.
What is SQL JOIN and why is it a vital skill for data people?
SQL JOIN is a clause in SQL used to combine rows from two or more data tables together, based on a common column that is related between them. This is a core skill for information retrieval in relational database management systems.
Anyone who works with databases knows that, in practice, data rarely resides obediently in a single table. To avoid duplication, people divide the data into multiple tables. In the Structured Query Language environment, JOIN is the bridge that helps you gather disparate pieces from many places into a complete reporting picture.
Without this skill, a data analyst or software engineer will be like a builder without cement, unable to connect the bricks together. If you are new to the industry and feel overwhelmed by the concepts, learning Learning MySQL basics for beginners is an extremely important first step to building a solid foundation.
Real story: First time "beginning" with JOIN and a bloody lesson
The most common mistake of SQL beginners is using the wrong JOIN type, leading to unreasonable missing or duplicated report data.
When I first started my programmer career, I once falsified the company's entire monthly revenue report just because of a disastrous mistake between LEFT JOIN and INNER JOIN. At that time, the boss asked for a list of all customers and the amount of money they spent. I used INNER JOIN, which resulted in the report losing half of the customer list (those who hadn't bought anything were filtered out).
That was a painful lesson that made me get "knocked on the head" by my boss and have to stay up all night to manually write down each line of how to use JOIN in SQL until I really absorbed its essence.
Understand the true nature: JOIN is not a simple "connection", it is a "relationship"
The nature of JOIN operations is based on the logical relationship between the primary key and foreign key of the data tables.
Many people mistakenly think that JOIN is simply a copy-paste operation and pastes two tables together. Not at all! It is a complex reconciliation process based on the strong association between the primary key (the unique identifier of a row) and the foreign key through a common column.
Ví dụ, bảng KhachHang có cột ID_Khach là khóa chính. Bảng DonHang có cột ID_Khach là khóa ngoại. Mệnh đề JOIN sẽ rà soát và ghép đúng ông khách A với đơn hàng của ông A. Để các phép truy vấn chạy mượt mà và chính xác, cấu trúc bảng phải được chuẩn hóa ngay từ đầu. Mình luôn khuyên các bạn trong team nên nắm vững kiến thức Thiết kế database chuẩn normalization để tránh các rắc rối về dữ liệu rác sau này.
The most common types of JOINs I use every day and how to distinguish them
To distinguish SQL JOIN types, you only need to remember the 4 most basic types: INNER JOIN (get the intersection), LEFT JOIN (get all on the left), RIGHT JOIN (get all on the right) and FULL OUTER JOIN (get all on both sides).
Distinguishing between SQL JOIN types is similar to a carpenter choosing the right saw for each type of wood. Using the wrong tools can still get the job done, but it will be very difficult and risky. Below are my practical experiences about SQL JOIN, explanations and examples for you to apply immediately to your project.
INNER JOIN: When you just need to find the most "faithful" part between two tables
INNER JOIN is a join that returns only records with matching values that exist in both queried tables.
So when to use INNER JOIN? That's when the business request is extremely clear: "Get me a list of students who HAVE registered for the course". You don't need to care about unregistered students, nor do you care about subjects that no one has taken. You just need to get the exact exact intersection core between the two boards. This type of JOIN is safe and is used with the highest frequency in Relational Database projects.
LEFT JOIN: "True love" when you need to get all the data from the original table on the left
LEFT JOIN returns all rows from the left table (first table) and matching rows from the right table; If there is no match, the return result will be NULL.
Imagine the Marketing department asks you to export a list of ALL users who have registered accounts on the app, along with their purchase history to run a "solicit" campaign. At this time, LEFT JOIN is "true love". Customers who have never purchased anything will still appear in the list, but their order information column will have the value NULL. Based on these NULL lines, the Marketing team will know exactly who needs to be sent the stimulus discount code.
Direct comparison of INNER JOIN and LEFT JOIN: When to use which one to avoid being "broken"?
The biggest difference when comparing INNER JOIN and LEFT JOIN is that INNER will completely remove mismatched records, while LEFT JOIN always fully preserves the data of the original table (left table).
When comparing INNER JOIN and LEFT JOIN, my working principle at Pham Hai is very simple. If you want "clean" data, certainly with a strong connection to revenue calculations: use INNER. If you want to keep the original list to make a comparison report, find "missing" or "dropped funnel" elements: you must use LEFT. Remember this principle carefully, you will never be criticized by your colleagues about the data.
RIGHT JOIN and FULL OUTER JOIN: Less used but a "secret weapon" in many cases
RIGHT JOIN works opposite to LEFT JOIN (giving priority to the right table), while FULL OUTER JOIN will bring all the data of both tables together regardless of whether there is a match or not.
In fact, in the past 10 years of working, I've found that RIGHT JOIN is rarely used because programmers often have the habit of reversing the positions of two tables and using LEFT JOIN for visual convenience and easier code reading.
What about FULL OUTER JOIN? It is a secret weapon when you need to synchronize or compare data between old and new systems. However, a small note is that some operating systems such as MySQL today (as of 2026) still do not support the FULL OUTER JOIN command directly. Instead, we often have to use the trick of combining LEFT JOIN and RIGHT JOIN through the UNION clause to produce equivalent results.
Syntax of JOIN types in SQL and detailed, easy-to-understand examples
The syntax of JOIN types in SQL generally follows a uniform standard, including the JOIN keyword to specify the type of connection and the ON clause to specify the connection condition.
Dù công ty bạn đang sử dụng SQL Server, MySQL hay PostgreSQL, cú pháp các loại JOIN trong SQL đều rất giống nhau. Một mẹo nhỏ nhưng có võ là hãy luôn sử dụng Alias bảng (đặt tên viết tắt cho bảng, ví dụ Customers c). Nó không chỉ giúp câu truy vấn dữ liệu của bạn ngắn gọn hơn mà còn tránh lỗi trùng lặp tên cột khi hai bảng có các cột trùng tên.
Standard syntax of INNER JOIN with ON clause
Cú pháp chuẩn: SELECT các_cột FROM BảngA INNER JOIN BảngB ON BảngA.CộtChung = BảngB.CộtChung;
Mệnh đề ON chính là trái tim của mọi phép JOIN. Nó đóng vai trò như một người chỉ đường, nói cho database biết: "Ê hệ thống, hãy ghép hai cái bảng này lại với nhau dựa trên cột ID này nhé". Nếu bạn quên hoặc viết sai điều kiện trong mệnh đề ON, câu truy vấn sẽ trả về kết quả sai lệch hoàn toàn hoặc báo lỗi syntax ngay lập tức.
Example INNER JOIN: Find a list of customers who have placed orders
The INNER JOIN example below will connect the Customers table and the Orders table to filter out those who actually initiated the transaction.
To illustrate the INNER JOIN example, let's say we have two simple data tables:
Bảng Customers (c):
| CustomerID | CustomerName |
|---|---|
| 1 | Pham Hai |
| 2 | Tran Binh |
| 3 | Le Loi |
Bảng Orders (o):
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 500,000 |
| 102 | 1 | 200,000 |
Câu truy vấn:
SELECT c.CustomerName, o.OrderID, o.Amount
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
Kết quả: Hệ thống chỉ trả về 2 dòng của "Phạm Hải" vì chỉ có ID số 1 là tồn tại ở cả hai bảng. "Trần Bình" và "Lê Lợi" bị loại bỏ hoàn toàn khỏi kết quả.
Syntax and example of LEFT JOIN: Get all products and quantities sold (including unsold products)
This LEFT JOIN example helps to retrieve the entire product list from the original table. For unsold products, the system will automatically fill in NULL in the sales column.
In this real-life LEFT JOIN example, my boss asked me to check to see if there were any unsold items in the warehouse that no one was buying.
Câu truy vấn:
SELECT p.ProductName, s.QuantitySold
FROM Products p
LEFT JOIN Sales s ON p.ProductID = s.ProductID;
Khi chạy đoạn code này, toàn bộ 1000 sản phẩm trong bảng Products (bảng bên trái) đều được hiển thị. Sản phẩm nào đã bán sẽ hiện số lượng ở cột QuantitySold. Sản phẩm nào ế, cột đó sẽ trống trơn (hiển thị chữ NULL). Nhờ đó, mình dễ dàng dùng thêm điều kiện WHERE s.QuantitySold IS NULL để lọc ra ngay danh sách hàng tồn kho.
RIGHT JOIN & FULL OUTER JOIN syntax and examples
The RIGHT JOIN example will retrieve all the data of the right table, while the FULL OUTER JOIN example will combine all the data of both tables together into one total table.
Below are RIGHT JOIN examples and FULL OUTER JOIN examples (written according to SQL Server/PostgreSQL support standards).
-- Ví dụ RIGHT JOIN: Lấy tất cả phòng ban, kể cả phòng ban chưa có nhân viên nào
SELECT e.EmpName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DeptID = d.DeptID;
-- Ví dụ FULL OUTER JOIN: Lấy tất cả nhân viên và tất cả phòng ban
SELECT e.EmpName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DeptID = d.DeptID;
Nhắc lại một lần nữa, nếu bạn đang dùng MySQL, hàm FULL OUTER JOIN sẽ bị báo lỗi. Bạn phải dùng LEFT JOIN ... UNION ... RIGHT JOIN để xử lý nhé.
Improve your skills: Other "money-making" JOIN techniques you should know
In addition to the 4 basic types of JOIN, mastering SELF JOIN, CROSS JOIN or the technique of JOIN multiple tables at the same time will help you solve complex data problems at an expert level.
At Pham Hai, we often face huge ERP or CRM systems, where data is not just in 2 tables but scattered in dozens of different tables. At this time, the advanced techniques below will help you optimize code lines, reduce server load and show the true class of a data worker.
What is SELF JOIN in SQL? Great trick when you need to compare data in the same table
SELF JOIN is a JOIN technique in which a table is connected to itself, often used to process hierarchical data such as personnel organization charts.
Nhiều bạn newbie hay hỏi mình SELF JOIN trong SQL là gì. Hãy tưởng tượng bạn có một bảng NhanVien. Trong đó có cột ID_NhanVien và cột ID_QuanLy (thực chất cũng là ID của một nhân viên khác trong cùng bảng đó). Để lấy ra danh sách "Tên nhân viên - Tên Quản lý", bạn dùng Alias tạo ra 2 bản sao ảo của bảng này (ví dụ nv và ql) rồi nối chúng lại bằng mệnh đề WHERE hoặc INNER JOIN. Rất ảo diệu nhưng cực kỳ hiệu quả!
CROSS JOIN: “Cartesian product” multiplication and practical use cases
CROSS JOIN is a join that creates a Cartesian product, meaning that each row of the first table will be combined with all rows of the second table without an ON condition.
So what is CROSS JOIN and why should you be careful with it? It creates aCartesian product. If table A has 100 rows, table B has 1000 rows, the resulting output will be 100,000 rows. Since there is no filtering condition, it is very easy to crash the server if you accidentally use it on large tables. I usually only use CROSS JOIN in specific cases such as: creating a mock data table, or needing to pair all "Shirt Sizes" (S, M, L) with all "Colors" (Blue, Red, Purple) to create a list of product variations.
SQL JOIN multiple tables: Experience in handling when needing to connect 3 or more tables
When performing SQL JOIN multiple tables, you need to write JOIN clauses one after another. The key is to determine the correct JOIN order so as not to bloat the intermediate data and slow down the system.
Trong thực tế, việc SQL JOIN nhiều bảng (từ 3 đến 5 bảng) là chuyện xảy ra như cơm bữa. Ví dụ: Từ bảng KháchHàng JOIN sang ĐơnHàng, rồi từ ĐơnHàng JOIN tiếp sang ChiTiếtĐơnHàng, cuối cùng JOIN sang SảnPhẩm. Kinh nghiệm "xương máu" của mình là: Hãy JOIN các bảng có kích thước nhỏ hoặc các bảng chứa điều kiện lọc gắt gao lên trước. Việc này giúp thu hẹp tập dữ liệu ngay từ đầu, góp phần cực lớn vào việc tối ưu hiệu suất truy vấn.
Conditional SQL JOIN: Add conditions with WHERE to filter results more precisely
Conditional SQL JOIN is the combination of the JOIN clause with the WHERE clause to both connect data and filter out records that satisfy specific business requirements.
SQL JOIN có điều kiện là kỹ năng cuối cùng chốt hạ vấn đề. Bạn đã kết nối thành công các bảng, nhưng sếp chỉ muốn xem doanh thu của "tháng 10 năm 2026" và "chỉ ở khu vực Hà Nội". Lúc này, bạn cứ viết JOIN bình thường, sau đó ném thêm mệnh đề WHERE vào cuối câu lệnh để gọt giũa kết quả. Để có thể ứng dụng trơn tru toàn bộ những kiến thức từ cơ sở dữ liệu lên giao diện web thực tế, các bạn có thể tham khảo thêm bài hướng dẫn Kết nối PHP MySQL CRUD hoàn chỉnh mà tụi mình đã biên soạn rất chi tiết.
In short, choosing the right JOIN type not only determines whether your report results are correct or incorrect, but also directly affects the speed and performance of the entire software system. Never just learn the syntax by rote. Through this article sharing about SQL JOIN types of explanations with examples, my sincere advice is that you create a small database on your computer, manually type each INNER, LEFT, RIGHT statement and observe the changes in the returned data. Using familiar hands or not, that is the shortest way for you to truly master this powerful "weapon".
Which type of JOIN do you most often encounter difficulty or confusion with during actual work? Please leave a comment below, me and the Pham Hai team will discuss and troubleshoot for you!
Lưu ý: Các thông tin trong bài viết này chỉ mang tính chất tham khảo. Để có hướng giải quyết tối ưu nhất, vui lòng liên hệ trực tiếp với chúng tôi để được tư vấn cụ thể dựa trên nhu cầu thực tế của bạn.
