Cách truy vấn nhiều bảng cơ sở dữ liệu SQL một lúc bằng mệnh đề Join

Cùng FUNiX khám phá cách truy xuất chính xác dữ liệu bạn muốn từ nhiều bảng cơ sở dữ liệu và các mệnh đề JOIN khác nhau cho phép bạn lấy kết quả chính xác mà bạn muốn.

1. Khởi tạo cơ sở dữ liệu mẫu

Nếu muốn làm theo các ví dụ trong bài viết này, bạn có thể khởi tạo cục bộ cơ sở dữ liệu mẫu bằng các lệnh bên dưới:

git clone https://github.com/mdizak/sample-select-db.git
cd sample-select-db
sudo mysql < store.sql
sudo mysql sampledb
mysql> SELECT COUNT(*) FROM customers;

Bạn sẽ nhận được kết quả cho biết có 2000 hàng trong bảng customers (khách hàng).

2. Mặc định / INNER Join

Mệnh đề Join mặc định được sử dụng trong cơ sở dữ liệu MySQL được gọi là INNER Join. Mệnh đề này trả về tất cả các bản ghi (record) có bản ghi trùng khớp trong cả hai bảng và loại bỏ tất cả các bản ghi khác.

Ví dụ: nếu bạn muốn xem họ và tên của khách hàng, cộng với số tiền và ngày đặt hàng cho tất cả các đơn hàng lớn hơn $1000, bạn có thể sử dụng câu lệnh SQL sau:

SELECT
c.id, c.first_name, c.last_name, o.amount, o.created_at
FROM
customers c, orders o
WHERE
o.customer_id = c.id AND o.amount >= 1000;

Một số lưu ý về truy vấn trên:

• Năm cột khác nhau đang được chọn, ba cột từ bảng customers (khách hàng) và hai cột từ bảng orders (đơn hàng).
• Trong mệnh đề FROM, hai bảng được xác định nhưng có hậu tố là các chữ cái “c” và “o”. Chúng chỉ đơn giản chỉ định các bí danh (alias) trong SQL, có thể là bất cứ thứ gì bạn muốn và được sử dụng để rút ngắn truy vấn SQL.
• o.customer_id = c.id là khía cạnh join của truy vấn và đảm bảo mối tương quan thích hợp giữa customers và orders.

Dưới đây là một cách khác và đúng về mặt cú pháp để viết cùng một truy vấn:

SELECT
c.id, c.first_name, c.last_name, o.amount, o.created_at
FROM
customers c INNER JOIN orders o
ON
customer_id = c.id
WHERE
o.amount >= 1000;

Truy vấn trên dễ đọc hơn một chút vì bạn có thể dễ dàng thấy sự kết hợp giữa bảng customers và orders. Tuy nhiên, hai truy vấn này giống nhau và sẽ tạo ra các bản ghi giống hệt nhau.

3. LEFT Joins

LEFT Joins sẽ trả về tất cả các bản ghi từ bảng bên trái cũng khớp với các bản ghi từ bảng bên phải và loại bỏ tất cả các bản ghi khác. Ví dụ: có thể bạn muốn xem tổng doanh số bán hàng (total amount) của từng sản phẩm (products) trong cơ sở dữ liệu, bạn có thể thử sử dụng truy vấn như:

SELECT
p.name, sum(item.amount) AS tamount
FROM
orders_items item LEFT JOIN products p
ON
item.product_id = p.id
GROUP BY item.product_id ORDER BY tamount DESC

Điều này dẫn đến chế độ xem hai cột hiển thị tên sản phẩm cùng với tổng doanh số bán hàng như mong đợi. Truy vấn đi qua tất cả các sản phẩm trong bảng order_items (sản phẩm được đặt hàng), nối chúng với các bản ghi trong bảng sản phẩm và trả về tổng doanh số của từng sản phẩm.

>>> Xem thêm: Cách sử dụng các câu lệnh truy vấn dữ liệu từ nhiều bảng (inner join, outer join, self join, union, except, intersection) trong SQL

4. RIGHT Joins

Sử dụng ví dụ trên, hãy lưu ý thực tế là truy vấn trên chỉ trả về 19 bản ghi trong khi có tổng cộng 22 sản phẩm trong cơ sở dữ liệu. Điều này là do truy vấn bắt đầu bằng bảng order_items rồi left join nó với bảng products (sản phẩm) và vì một số sản phẩm chưa bao giờ được đặt hàng nên không có bản ghi nào về chúng tồn tại trong bảng order_items.

Điều gì sẽ xảy ra nếu bạn muốn nhận danh sách tất cả các sản phẩm có doanh số bán hàng, bao gồm cả những sản phẩm chưa được đặt hàng? Hãy thử right join với truy vấn sau:

SELECT
p.name, sum(item.amount) AS tamount
FROM
orders_items item RIGHT JOIN products p
ON
item.product_id = p.id
GROUP BY p.id ORDER BY tamount DESC

Truy vấn hiện trả về đầy đủ 22 sản phẩm với ba trong số đó có số lượng null. Điều này là do thay vì sử dụng order_items làm bảng chính join với bảng products, right join sẽ đi theo hướng ngược lại và join bảng products với bảng order_items.

5. Nhiều mệnh đề join trong một truy vấn

Đôi khi bạn có nhu cầu join ba bảng trở lên với nhau để có được một tập hợp kết quả cụ thể.

Ví dụ: có thể bạn muốn có danh sách tất cả khách hàng đã mua lò vi sóng (id sản phẩm số 1), bao gồm tên và ngày đặt hàng của họ. Điều này yêu cầu SELECT trên ba bảng có thể được thực hiện bằng cách sử dụng hai mệnh đề join với truy vấn sau:

SELECT
c.first_name, c.last_name, o.amount, o.created_at
FROM
customers c INNER JOIN orders o
ON
c.id = o.customer_id INNER JOIN orders_items item
ON
item.order_id = o.id
WHERE
item.product_id = 1 ORDER BY o.created_at;

Truy vấn này trả về tất cả 426 đơn hàng của lò vi sóng. Trước tiên, nó khớp tất cả các khách hàng với các đơn đặt hàng tương ứng của họ, sau đó truy vấn thêm các kết quả được đặt bằng cách chỉ khớp tất cả các đơn hàng với những đơn hàng trong bảng order_items có chứa sản phẩm vi sóng (id# 1).

>>> Xem thêm: Cách truy vấn nhiều bảng cơ sở dữ liệu SQL một lúc bằng mệnh đề Join

6. Không bao giờ sử dụng truy vấn phụ với mệnh đề IN

Bạn phải luôn tránh sử dụng các truy vấn phụ trong các truy vấn SQL của mình, chẳng hạn như:

SELECT first_name,last_name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'approved' AND amount < 100);

Các truy vấn như trên rất kém hiệu quả, sử dụng nhiều tài nguyên. Thay vào đó, hãy sử dụng các mệnh đề join thích hợp như đã nêu trong các phần trên. Ví dụ: truy vấn trên nên được viết lại thành:

SELECT c.first_name, c.last_name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'approved' AND o.amount < 100;

7. Tiết kiệm thời gian với mệnh đề join

Bài viết này hy vọng sẽ giúp bạn thấy được sức mạnh của cơ sở dữ liệu quan hệ như MySQL và cách xây dựng các truy vấn SQL truy xuất các bản ghi từ nhiều bảng trong một truy vấn bằng cách sử dụng các mệnh đề join, cho phép bạn truy xuất kết quả chính xác như mong muốn.

Tìm hiểu ngay chương trình học công nghệ thông tin trực tuyến tại FUNiX ở đây:

Vân Nguyễn

Dịch từ: https://www.makeuseof.com/how-to-query-multiple-database-tables-with-sql-joins/