GALAXY TECH
Chuyên gia Công nghệ
Thứ ba, 20/10/2020 | 00:00 GMT+7

Sử dụng Joins trong sql

Nhiều thiết kế database tách thông tin thành các bảng khác nhau dựa trên mối quan hệ giữa các điểm dữ liệu nhất định. Ngay cả trong những trường hợp như thế này, có khả năng sẽ có lúc ai đó muốn truy xuất thông tin từ nhiều bảng cùng một lúc.

Một cách phổ biến để truy cập dữ liệu từ nhiều bảng trong một thao tác Ngôn ngữ truy vấn có cấu trúc (SQL) là kết hợp các bảng với một mệnh đề JOIN . Dựa trên các phép toán nối trong đại số quan hệ, mệnh đề JOIN kết hợp các bảng riêng biệt bằng cách so khớp các hàng trong mỗi bảng có liên quan với nhau. Thông thường, mối quan hệ này dựa trên một cặp cột - một từ mỗi bảng - chia sẻ các giá trị chung, chẳng hạn như khóa ngoại của một bảng và khóa chính của bảng khác mà foreign keys tham chiếu.

Hướng dẫn này phác thảo cách tạo nhiều truy vấn SQL bao gồm mệnh đề JOIN . Nó cũng nêu bật các loại mệnh đề JOIN khác nhau, cách chúng kết hợp dữ liệu từ nhiều bảng và cách đặt alias tên cột để làm cho việc viết các hoạt động JOIN bớt tẻ nhạt hơn.

Yêu cầu

Để làm theo hướng dẫn này, bạn cần một máy tính chạy một số loại hệ quản trị database quan hệ (RDBMS) sử dụng SQL. Các hướng dẫn và ví dụ trong hướng dẫn này đã được kiểm nghiệm bằng cách sử dụng môi trường sau:

Lưu ý : Xin lưu ý nhiều RDBMS sử dụng các triển khai SQL duy nhất của riêng họ. Mặc dù các lệnh được nêu trong hướng dẫn này sẽ hoạt động trên hầu hết các RDBMS, nhưng cú pháp hoặc kết quả chính xác có thể khác nếu bạn kiểm tra chúng trên một hệ thống không phải MySQL.

  • Bạn cũng cần một database với một số bảng được tải với dữ liệu mẫu mà bạn có thể sử dụng để thực hành sử dụng các hoạt động JOIN . Ta khuyến khích bạn xem qua phần Kết nối với MySQL và Cài đặt Database Mẫu sau đây để biết chi tiết về cách kết nối với server MySQL và tạo database thử nghiệm được sử dụng trong các ví dụ xuyên suốt hướng dẫn này.

Kết nối với MySQL và cài đặt database mẫu

Nếu hệ thống database SQL của bạn chạy trên một server từ xa, hãy SSH vào server từ máy local của bạn:

  • ssh sammy@your_server_ip

Sau đó, mở dấu nhắc server MySQL, thay thế sammy bằng tên account user MySQL của bạn:

  • mysql -u sammy -p

Tạo database có tên là joinsDB :

  • CREATE DATABASE joinsDB;

Nếu database được tạo thành công, bạn sẽ nhận được kết quả như sau:

Output
Query OK, 1 row affected (0.01 sec)

Để chọn database joinsDB , hãy chạy câu lệnh USE sau:

  • USE joinsDB;
Output
Database changed

Sau khi chọn joinsDB , hãy tạo một vài bảng bên trong nó. Đối với các ví dụ được sử dụng trong hướng dẫn này, hãy tưởng tượng rằng bạn điều hành một nhà máy và đã quyết định bắt đầu theo dõi thông tin về dòng sản phẩm, nhân viên trong group bán hàng và doanh số bán hàng của công ty bạn trong database SQL. Bạn dự định bắt đầu với ba bảng, bảng đầu tiên sẽ lưu trữ thông tin về sản phẩm của bạn. Bạn quyết định bảng đầu tiên này cần ba cột:

  • productID : số nhận dạng của mỗi sản phẩm, được biểu thị bằng kiểu dữ liệu int . Cột này sẽ đóng role là khóa chính của bảng, nghĩa là mỗi giá trị sẽ hoạt động như một mã định danh duy nhất cho hàng tương ứng của nó. Bởi vì mọi giá trị trong khóa chính phải là duy nhất, cột này cũng sẽ có ràng buộc UNIQUE được áp dụng cho nó
  • productName : tên của mỗi sản phẩm, được thể hiện bằng kiểu dữ liệu varchar với tối đa 20 ký tự
  • price : giá của từng sản phẩm, được biểu thị bằng kiểu dữ liệu decimal . Câu lệnh này chỉ định rằng bất kỳ giá trị nào trong cột này được giới hạn ở độ dài tối đa là bốn chữ số với hai trong số những chữ số đó ở bên phải dấu thập phân. Do đó, phạm vi giá trị được phép trong cột này từ -99.99 đến 99.99

Tạo một bảng có tên products có ba cột sau:

  • CREATE TABLE products (
  • productID int UNIQUE,
  • productName varchar(20),
  • price decimal (4,2),
  • PRIMARY KEY (productID)
  • );

Bảng thứ hai sẽ lưu trữ thông tin về các nhân viên trong đội bán hàng của công ty bạn. Bạn quyết định bảng này cũng cần ba cột:

  • empID : tương tự như cột productID , cột này sẽ chứa một số nhận dạng duy nhất cho mỗi nhân viên trong group bán hàng được thể hiện với kiểu dữ liệu int . Tương tự như vậy, cột này sẽ có một ràng buộc UNIQUE được áp dụng cho nó và sẽ đóng role là khóa chính cho bảng team
  • empName : tên của từng nhân viên bán hàng, được thể hiện bằng kiểu dữ liệu varchar với tối đa 20 ký tự
  • productSpecialty : mỗi thành viên trong group bán hàng của bạn đã được chỉ định một sản phẩm làm chuyên môn của họ; họ có thể bán bất kỳ sản phẩm nào mà công ty bạn production , nhưng tổng thể của họ sẽ tập trung vào bất kỳ sản phẩm nào họ chuyên về. Để chỉ ra điều này trong bảng, bạn tạo cột này chứa giá trị productID của bất kỳ sản phẩm nào mà mỗi nhân viên chuyên về

Để đảm bảo cột productSpecialty chỉ chứa các giá trị đại diện cho số ID sản phẩm hợp lệ, bạn quyết định áp dụng ràng buộc khóa ngoại cho cột tham chiếu đến cột productID của bảng products . Ràng buộc foreign keys là một cách để thể hiện mối quan hệ giữa hai bảng bằng cách yêu cầu các giá trị trong cột mà nó áp dụng phải tồn tại trong cột mà nó tham chiếu. Trong câu CREATE TABLE sau đây, ràng buộc FOREIGN KEY yêu cầu bất kỳ giá trị nào được thêm vào cột productSpecialty trong bảng team phải tồn tại trong cột productID của bảng products .

Tạo một bảng có tên team với ba cột sau:

  • CREATE TABLE team (
  • empID int UNIQUE,
  • empName varchar(20),
  • productSpecialty int,
  • PRIMARY KEY (empID),
  • FOREIGN KEY (productSpecialty) REFERENCES products (productID)
  • );

Bảng cuối cùng bạn tạo sẽ lưu giữ profile về doanh số bán hàng của công ty. Bảng này sẽ có bốn cột:

  • saleID : tương tự như cột productID và empID , cột này sẽ chứa một số nhận dạng duy nhất cho mỗi lần bán hàng được biểu thị bằng kiểu dữ liệu int . Cột này cũng sẽ có một ràng buộc UNIQUE để nó có thể dùng làm khóa chính cho bảng sales
  • quantity : số lượng đơn vị của mỗi sản phẩm đã bán, được biểu thị bằng kiểu dữ liệu int
  • productID : số nhận dạng của sản phẩm đã bán, được biểu thị dưới dạng int
  • salesperson : số nhận dạng của nhân viên đã bán hàng

Giống như cột productSpecialty từ bảng team , bạn quyết định áp dụng các ràng buộc FOREIGN KEY cho cả cột productID và salesperson . Điều này sẽ đảm bảo các cột này chỉ chứa các giá trị đã tồn tại trong cột productID của bảng products và cột empID của bảng team , tương ứng.

Tạo một bảng có tên sales với bốn cột sau:

  • CREATE TABLE sales (
  • saleID int UNIQUE,
  • quantity int,
  • productID int,
  • salesperson int,
  • PRIMARY KEY (saleID),
  • FOREIGN KEY (productID) REFERENCES products (productID),
  • FOREIGN KEY (salesperson) REFERENCES team (empID)
  • );

Sau đó, tải bảng products với một số dữ liệu mẫu bằng cách chạy thao tác INSERT INTO sau:

  • INSERT INTO products
  • VALUES
  • (1, 'widget', 18.99),
  • (2, 'gizmo', 14.49),
  • (3, 'thingamajig', 39.99),
  • (4, 'doodad', 11.50),
  • (5, 'whatzit', 29.99);

Sau đó tải bảng team với một số dữ liệu mẫu:

  • INSERT INTO team
  • VALUES
  • (1, 'Florence', 1),
  • (2, 'Mary', 4),
  • (3, 'Diana', 3),
  • (4, 'Betty', 2);

Tải bảng sales với một số dữ liệu mẫu:

  • INSERT INTO sales
  • VALUES
  • (1, 7, 1, 1),
  • (2, 10, 5, 4),
  • (3, 8, 2, 4),
  • (4, 1, 3, 3),
  • (5, 5, 1, 3);

Cuối cùng, hãy tưởng tượng rằng công ty của bạn thực hiện một vài đợt bán hàng mà không có sự tham gia của bất kỳ ai trong group bán hàng của bạn. Để ghi lại những lần bán hàng này, hãy chạy thao tác sau để thêm ba hàng vào bảng sales không bao gồm giá trị cho cột salesperson :

  • INSERT INTO sales (saleID, quantity, productID)
  • VALUES
  • (6, 1, 5),
  • (7, 3, 1),
  • (8, 4, 5);

Như vậy, bạn đã sẵn sàng làm theo phần còn lại của hướng dẫn và bắt đầu tìm hiểu về cách nối các bảng với nhau trong SQL.

Hiểu cú pháp của các phép toán JOIN

JOIN mệnh đề JOIN được dùng trong nhiều câu lệnh SQL khác nhau, bao gồm các hoạt động UPDATE và DELETE . Tuy nhiên, với mục đích minh họa, các ví dụ trong hướng dẫn này sử dụng truy vấn SELECT để chứng minh cách hoạt động của mệnh đề JOIN .

Ví dụ sau cho thấy cú pháp chung của một SELECT bao gồm một mệnh đề JOIN :

  • SELECT table1.column1, table2.column2
  • FROM table1 JOIN table2
  • ON search_condition;

Cú pháp này bắt đầu bằng SELECT sẽ trả về hai cột từ hai bảng riêng biệt. Lưu ý vì mệnh đề JOIN so sánh nội dung của nhiều hơn một bảng, nên cú pháp ví dụ này chỉ định bảng để chọn từng cột bằng cách đặt trước tên của cột với tên của bảng và một dấu chấm. Đây được gọi là tham chiếu cột đủ điều kiện .

Bạn có thể sử dụng các tham chiếu cột đủ điều kiện như thế này trong bất kỳ thao tác nào, nhưng về mặt kỹ thuật, làm như vậy chỉ cần thiết trong các thao tác mà hai cột từ các bảng khác nhau có cùng tên. Tuy nhiên, bạn nên sử dụng chúng khi làm việc với nhiều bảng vì chúng có thể giúp làm cho các hoạt động JOIN dễ đọc và dễ hiểu hơn.

Sau mệnh đề SELECT mệnh đề FROM . Trong bất kỳ truy vấn nào, mệnh đề FROM là nơi bạn xác định tập dữ liệu sẽ được tìm kiếm để trả về dữ liệu mong muốn. Sự khác biệt duy nhất ở đây là mệnh đề FROM bao gồm hai bảng được phân tách bằng từ khóa JOIN . Một cách hữu ích để nghĩ về việc viết truy vấn là hãy nhớ rằng bạn SELECT cột nào để trả về FROM bảng bạn muốn truy vấn.

Theo sau đó là mệnh đề ON , mô tả cách truy vấn nối hai bảng với nhau bằng cách xác định điều kiện tìm kiếm . Điều kiện tìm kiếm là một tập hợp một hoặc nhiều vị từ hoặc biểu thức có thể đánh giá xem một điều kiện nhất định là “đúng”, “sai” hay “không xác định”. Có thể hữu ích khi nghĩ về một phép toán JOIN như là kết hợp mọi hàng từ cả hai bảng và sau đó trả về bất kỳ hàng nào mà điều kiện tìm kiếm trong mệnh đề ON đánh giá là “true”.

Trong mệnh đề ON , thường có ý nghĩa khi bao gồm điều kiện tìm kiếm kiểm tra xem hai cột liên quan - như foreign keys của một bảng và khóa chính của bảng khác mà foreign keys tham chiếu - có giá trị bằng nhau hay không. Điều này đôi khi được gọi là một phép nối trang bị .

Như một ví dụ về cách trang bị kết hợp dữ liệu khớp từ nhiều bảng, hãy chạy truy vấn sau bằng cách sử dụng dữ liệu mẫu bạn đã thêm trước đó. Câu lệnh này sẽ nối các products và bảng team với điều kiện tìm kiếm kiểm tra các giá trị phù hợp trong các cột productID và productSpecialty tương ứng của chúng. Sau đó, nó sẽ trả về tên của mọi thành viên trong group bán hàng, tên của từng sản phẩm mà họ chuyên về và giá của những sản phẩm đó:

  • SELECT team.empName, products.productName, products.price
  • FROM products JOIN team
  • ON products.productID = team.productSpecialty;

Đây là tập hợp kết quả của truy vấn này:

Output
+----------+-------------+-------+ | empName  | productName | price | +----------+-------------+-------+ | Florence | widget      | 18.99 | | Mary     | doodad      | 11.50 | | Diana    | thingamajig | 39.99 | | Betty    | gizmo       | 14.49 | +----------+-------------+-------+ 4 rows in set (0.00 sec)

Để minh họa cách SQL kết hợp các bảng này để tạo thành tập kết quả này, ta hãy xem xét kỹ hơn quá trình này. Để rõ ràng, điều sau đây không phải là chính xác những gì sẽ xảy ra khi hệ thống quản lý database kết hợp hai bảng với nhau, nhưng có thể hữu ích khi nghĩ các hoạt động JOIN như sau một thủ tục như thế này.

Đầu tiên, truy vấn in mọi hàng và cột trong bảng đầu tiên trong mệnh đề FROM , products :

JOIN Process Example
+-----------+-------------+-------+ | productID | productName | price | +-----------+-------------+-------+ |         1 | widget      | 18.99 | |         2 | gizmo       | 14.49 | |         3 | thingamajig | 39.99 | |         4 | doodad      | 11.50 | |         5 | whatzit     | 29.99 | +-----------+-------------+-------+

Sau đó, nó sẽ xem xét từng hàng này và trùng với bất kỳ hàng nào từ bảng team có productSpecialty bằng với giá trị productID trong hàng đó:

JOIN Process Example
+-----------+-------------+-------+-------+----------+------------------+ | productID | productName | price | empID | empName  | productSpecialty | +-----------+-------------+-------+-------+----------+------------------+ |         1 | widget      | 18.99 |     1 | Florence |                1 | |         2 | gizmo       | 14.49 |     4 | Betty    |                2 | |         3 | thingamajig | 39.99 |     3 | Diana    |                3 | |         4 | doodad      | 11.50 |     2 | Mary     |                4 | |         5 | whatzit     | 29.99 |       |          |                  | +-----------+-------------+-------+-------+----------+------------------+

Sau đó, nó cắt bất kỳ hàng nào không khớp và sắp xếp lại các cột dựa trên thứ tự của chúng trong mệnh đề SELECT , loại bỏ bất kỳ cột nào không được chỉ định, nghỉ dưỡng các hàng và trả về tập kết quả cuối cùng:

JOIN Process Example
+----------+-------------+-------+ | empName  | productName | price | +----------+-------------+-------+ | Florence | widget      | 18.99 | | Mary     | doodad      | 11.50 | | Diana    | thingamajig | 39.99 | | Betty    | gizmo       | 14.49 | +----------+-------------+-------+ 4 rows in set (0.00 sec)

Sử dụng phép nối Equi là cách phổ biến nhất để nối các bảng, nhưng có thể sử dụng các toán tử SQL khác như < , > , LIKE , NOT LIKE hoặc thậm chí BETWEEN trong điều kiện tìm kiếm mệnh đề ON . Tuy nhiên, hãy lưu ý việc sử dụng các điều kiện tìm kiếm phức tạp hơn có thể gây khó khăn cho việc dự đoán dữ liệu nào sẽ xuất hiện trong tập kết quả.

Trong hầu hết các triển khai, bạn có thể nối các bảng với bất kỳ tập hợp cột nào có tiêu chuẩn SQL đề cập đến kiểu dữ liệu " JOIN đủ điều kiện". Điều này nghĩa là , nói chung, có thể nối một cột chứa dữ liệu số với bất kỳ cột nào khác chứa dữ liệu số, dù kiểu dữ liệu tương ứng của chúng là gì. Tương tự, thường có thể nối bất kỳ cột nào chứa giá trị ký tự với bất kỳ cột nào khác chứa dữ liệu ký tự. Tuy nhiên, như đã nêu trước đó, các cột bạn khớp để nối hai bảng thường sẽ là những cột đã biểu thị mối quan hệ giữa các bảng, như foreign keys và khóa chính của bảng khác mà nó tham chiếu.

Nhiều triển khai SQL cũng cho phép bạn nối các cột có cùng tên với từ khóa USING thay vì ON . Đây là cách cú pháp của một hoạt động như vậy có thể trông:

  • SELECT table1.column1, table2.column2
  • FROM table1 JOIN table2
  • USING (related_column);

Trong cú pháp ví dụ này, mệnh đề USING tương đương với ON table1 . related_column = table2 . related_column ; .

Vì sales và products đều có một cột có tên productID , bạn có thể kết hợp chúng bằng cách đối sánh các cột này với từ khóa USING . Lệnh sau thực hiện việc này và trả về saleID của mỗi lần bán hàng, số lượng đơn vị đã bán, tên của mỗi sản phẩm đã bán và giá của nó. Ngoài ra, nó sắp xếp kết quả được đặt theo thứ tự tăng dần dựa trên giá trị saleID :

  • SELECT sales.saleID, sales.quantity, products.productName, products.price
  • FROM sales JOIN products
  • USING (productID)
  • ORDER BY saleID;
Output
+--------+----------+-------------+-------+ | saleID | quantity | productName | price | +--------+----------+-------------+-------+ |      1 |        7 | widget     | 18.99 | |      2 |       10 | whatzit     | 29.99 | |      3 |        8 | gizmo       | 14.49 | |      4 |        1 | thingamajig | 39.99 | |      5 |        5 | widget      | 18.99 | |      6 |        1 | whatzit     | 29.99 | |      7 |        3 | widget      | 18.99 | |      8 |        4 | whatzit     | 29.99 | +--------+----------+-------------+-------+ 8 rows in set (0.00 sec)

Khi nối các bảng, hệ thống database đôi khi sẽ sắp xếp lại các hàng theo những cách không dễ dự đoán. Bao gồm một mệnh đề ORDER BY như thế này có thể giúp tập hợp kết quả mạch lạc và dễ đọc hơn.

Tham gia nhiều hơn hai bảng

Có thể đôi khi bạn cần kết hợp dữ liệu từ nhiều hơn hai bảng. Bạn có thể nối bất kỳ số lượng bảng nào lại với nhau bằng cách nhúng các mệnh đề JOIN trong các mệnh đề JOIN khác. Cú pháp sau là một ví dụ về cách điều này có thể trông như thế nào khi kết hợp ba bảng:

  • SELECT table1.column1, table2.column2, table3.column3
  • FROM table1 JOIN table2
  • ON table1.related_column = table2.related_column
  • JOIN table3
  • ON table3.related_column = table1_or_2.related_column;

Mệnh đề FROM của cú pháp ví dụ này bắt đầu bằng cách nối table1 với table2 . Sau mệnh đề ON của phép nối này, nó bắt đầu một JOIN thứ hai kết hợp tập hợp ban đầu của các bảng đã tham gia với table3 . Lưu ý bảng thứ ba có thể được nối với một cột trong bảng thứ nhất hoặc thứ hai.

Để minh họa, hãy tưởng tượng rằng bạn muốn biết doanh số bán hàng của nhân viên đã mang lại bao nhiêu, nhưng bạn chỉ quan tâm đến profile bán hàng liên quan đến một nhân viên bán sản phẩm mà họ chuyên về.

Để có được thông tin này, bạn có thể chạy truy vấn sau. Truy vấn này bắt đầu bằng cách kết hợp các products và bảng sales với nhau bằng cách khớp các cột productID tương ứng của chúng. Sau đó, nó tham gia bảng team với hai bảng đầu tiên bằng cách khớp từng hàng trong JOIN đầu tiên với cột productSpecialty của nó. Sau đó, truy vấn lọc kết quả bằng WHERE để chỉ trả về các hàng trong đó nhân viên phù hợp cũng là người đã bán hàng. Truy vấn này cũng bao gồm mệnh đề ORDER BY sắp xếp kết quả cuối cùng theo thứ tự tăng dần dựa trên giá trị trong cột saleID :

  • SELECT sales.saleID,
  • team.empName,
  • products.productName,
  • (sales.quantity * products.price)
  • FROM products JOIN sales
  • USING (productID)
  • JOIN team
  • ON team.productSpecialty = sales.productID
  • WHERE team.empID = sales.salesperson
  • ORDER BY sales.saleID;

Lưu ý trong số các cột được liệt kê trong mệnh đề SELECT của truy vấn này là một biểu thức nhân các giá trị trong cột quantity của bảng sales với products price trị price của bảng products . Nó trả về sản phẩm của các giá trị này trong các hàng phù hợp:

Output
+--------+----------+-------------+-----------------------------------+ | saleID | empName  | productName | (sales.quantity * products.price) | +--------+----------+-------------+-----------------------------------+ |      1 | Florence | widget      |                            132.93 | |      3 | Betty    | gizmo       |                            115.92 | |      4 | Diana    | thingamajig |                             39.99 | +--------+----------+-------------+-----------------------------------+ 3 rows in set (0.00 sec)

Tất cả các ví dụ cho đến nay đều có cùng một loại mệnh đề JOIN : INNER JOIN . Để biết tổng quan về các phép nối INNER , phép nối OUTER và sự khác nhau của chúng, hãy tiếp tục đọc phần tiếp theo.

Nội vs Outer JOIN Operations

Có hai loại mệnh đề JOIN : tham gia INNER và OUTER gia OUTER . Sự khác biệt giữa hai loại kết hợp này liên quan đến dữ liệu mà chúng trả về. INNER thao tác kết hợp INNER chỉ trả về các hàng phù hợp từ mỗi bảng đã tham gia, trong khi các phép nối OUTER trả về cả các hàng phù hợp và không khớp.

Các cú pháp mẫu và truy vấn từ các phần trước đều sử dụng mệnh đề INNER JOIN mặc dù không có mệnh đề nào trong số đó bao gồm từ khóa INNER . Hầu hết các triển khai SQL coi bất kỳ mệnh đề JOIN nào là một phép nối INNER trừ khi được quy định rõ ràng khác.

Các truy vấn chỉ định OUTER JOIN kết hợp nhiều bảng và trả về bất kỳ hàng nào phù hợp cũng như các hàng không khớp. Điều này có thể hữu ích để tìm các hàng có giá trị bị thiếu hoặc trong trường hợp có thể chấp nhận các kết quả phù hợp từng phần.

OUTER thao tác nối OUTER có thể được chia thành ba loại: phép nối LEFT OUTER , phép nối RIGHT OUTER nối FULL OUTER . LEFT OUTER tham gia hoặc chỉ tham gia LEFT , trả về mọi hàng phù hợp từ hai bảng đã kết hợp, cũng như mọi hàng không khớp từ bảng “bên trái”. Trong ngữ cảnh của các hoạt động JOIN , bảng “bên trái” luôn là bảng đầu tiên được chỉ định ngay sau từ khóa FROM và ở bên trái của từ khóa JOIN . Tương tự như vậy, bảng “bên phải” là bảng thứ hai hoặc bảng ngay sau JOIN và các phép tham gia RIGHT OUTER trả về mọi hàng phù hợp từ các bảng đã tham gia cùng với mọi hàng không khớp từ bảng “bên phải”. FULL OUTER JOIN trả về mọi hàng từ cả hai bảng, bao gồm bất kỳ hàng nào từ một trong hai bảng không có khớp.

Để minh họa cách các loại mệnh đề JOIN khác nhau này trả về dữ liệu, hãy chạy các truy vấn ví dụ sau trên các bảng được tạo trong phần phụ Kết nối và Cài đặt Database Mẫu trước đó. Các truy vấn này giống hệt nhau ngoại trừ mỗi truy vấn chỉ định một loại mệnh đề JOIN khác nhau.

Ví dụ đầu tiên này sử dụng INNER JOIN để kết hợp các bảng sales và team với nhau bằng cách khớp các cột salesperson và empID tương ứng của họ. , từ khóa INNER được ngụ ý mặc dù nó không được bao gồm một cách rõ ràng:

  • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
  • FROM sales JOIN team
  • ON sales.salesperson = team.empID;

Vì truy vấn này sử dụng mệnh đề INNER JOIN , nó chỉ trả về các hàng phù hợp từ cả hai bảng:

Output
+--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName  | +--------+----------+-------------+----------+ |      1 |        7 |           1 | Florence | |      4 |        1 |           3 | Diana    | |      5 |        5 |           3 | Diana    | |      2 |       10 |           4 | Betty    | |      3 |        8 |           4 | Betty    | +--------+----------+-------------+----------+ 5 rows in set (0.00 sec)

Phiên bản này của truy vấn sử dụng mệnh đề LEFT OUTER JOIN thay thế:

  • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
  • FROM sales LEFT OUTER JOIN team
  • ON sales.salesperson = team.empID;

Giống như truy vấn trước, truy vấn này cũng trả về mọi giá trị phù hợp từ cả hai bảng. Tuy nhiên, nó cũng trả về bất kỳ giá trị nào từ bảng “bên trái” (trong trường hợp này là sales ) không khớp trong bảng “bên phải” ( team ). Bởi vì các hàng này trong bảng bên trái không khớp ở bên phải, các giá trị chưa khớp được trả về dưới dạng NULL :

Output
+--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName  | +--------+----------+-------------+----------+ |      1 |        7 |           1 | Florence | |      2 |       10 |           4 | Betty    | |      3 |        8 |           4 | Betty    | |      4 |        1 |           3 | Diana    | |      5 |        5 |           3 | Diana    | |      6 |        1 |        NULL | NULL     | |      7 |        3 |        NULL | NULL     | |      8 |        4 |        NULL | NULL     | +--------+----------+-------------+----------+ 8 rows in set (0.00 sec)

Thay vào đó, version tiếp theo của truy vấn này sử dụng mệnh đề RIGHT JOIN :

  • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
  • FROM sales RIGHT JOIN team
  • ON sales.salesperson = team.empID;

Lưu ý mệnh đề JOIN của truy vấn này đọc RIGHT JOIN thay vì RIGHT OUTER JOIN . Tương tự như cách từ khóa INNER không bắt buộc phải chỉ định mệnh đề INNER JOIN , OUTER được ngụ ý khi nào bạn viết LEFT JOIN hoặc RIGHT JOIN .

Kết quả của truy vấn này trái ngược với kết quả trước đó ở chỗ nó trả về mọi hàng từ cả hai bảng, nhưng chỉ những hàng chưa khớp từ bảng “bên phải”:

Output
+--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName  | +--------+----------+-------------+----------+ |      1 |        7 |           1 | Florence | |   NULL |     NULL |        NULL | Mary     | |      4 |        1 |           3 | Diana    | |      5 |        5 |           3 | Diana    | |      2 |       10 |           4 | Betty    | |      3 |        8 |           4 | Betty    | +--------+----------+-------------+----------+ 6 rows in set (0.00 sec)

Lưu ý : Lưu ý rằng MySQL không hỗ trợ mệnh đề FULL OUTER JOIN . Để minh họa dữ liệu nào mà truy vấn này sẽ trả về nếu nó sử dụng mệnh đề FULL OUTER JOIN , đây là bộ kết quả sẽ trông như thế nào trên database PostgreSQL:

  • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
  • FROM sales FULL OUTER JOIN team
  • ON sales.salesperson = team.empID;
Output
 saleid | quantity | salesperson | empname   --------+----------+-------------+----------       1 |        7 |           1 | Florence       2 |       10 |           4 | Betty       3 |        8 |           4 | Betty       4 |        1 |           3 | Diana       5 |        5 |           3 | Diana       6 |        1 |             |        7 |        3 |             |        8 |        4 |             |          |          |             | Mary (9 rows)

Như kết quả kết quả này cho biết, FULL JOIN trả về mọi hàng trong cả hai bảng bao gồm cả những hàng chưa khớp.

Đặt tên cho bảng và tên cột trong điều khoản JOIN

Khi nối các bảng với tên dài hoặc có tính mô tả cao, việc phải viết nhiều tham chiếu cột đủ tiêu chuẩn có thể trở nên tẻ nhạt. Để tránh điều này, user đôi khi thấy hữu ích khi cung cấp tên bảng hoặc cột với alias ngắn hơn.

Bạn có thể thực hiện điều này trong SQL theo bất kỳ định nghĩa bảng nào trong mệnh đề FROM với từ khóa AS , sau đó theo sau đó với alias bạn chọn:

  • SELECT t1.column1, t2.column2
  • FROM table1 AS t1 JOIN table2 AS t2
  • ON t1.related_column = t2.related_column;

Cú pháp ví dụ này sử dụng alias trong mệnh đề SELECT mặc dù chúng không được định nghĩa cho đến mệnh đề FROM . Điều này có thể xảy ra bởi vì, trong các truy vấn SQL, thứ tự thực hiện bắt đầu bằng mệnh đề FROM . Điều này có thể gây nhầm lẫn, nhưng sẽ hữu ích khi nhớ điều này và nghĩ về alias của bạn trước khi bắt đầu viết truy vấn.

Ví dụ: hãy chạy truy vấn sau kết hợp các bảng sales và products và cung cấp cho chúng các alias S và P , tương ứng:

  • SELECT S.saleID, S.quantity,
  • P.productName,
  • (P.price * S.quantity) AS revenue
  • FROM sales AS S JOIN products AS P
  • USING (productID);

Lưu ý ví dụ này cũng tạo alias thứ ba, revenue , cho sản phẩm của các giá trị trong cột quantity của bảng sales và giá trị khớp của chúng từ cột price của bảng products . Điều này chỉ rõ ràng ở tên cột trong tập kết quả, nhưng việc cung cấp các alias như thế này có thể hữu ích để truyền đạt ý nghĩa hoặc mục đích đằng sau kết quả truy vấn:

Output
+--------+----------+-------------+---------+ | saleID | quantity | productName | revenue | +--------+----------+-------------+---------+ |      1 |        7 | widget      |  132.93 | |      2 |       10 | whatzit     |  299.90 | |      3 |        8 | gizmo       |  115.92 | |      4 |        1 | thingamajig |   39.99 | |      5 |        5 | widget      |   94.95 | |      6 |        1 | whatzit     |   29.99 | |      7 |        3 | widget      |   56.97 | |      8 |        4 | whatzit     |  119.96 | +--------+----------+-------------+---------+ 8 rows in set (0.00 sec)

Lưu ý khi xác định alias , về mặt kỹ thuật, từ khóa AS là tùy chọn. Ví dụ trước cũng có thể được viết như thế này:

  • SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue
  • FROM sales S JOIN products P
  • USING (productID);

Mặc dù từ khóa AS không cần thiết để xác định alias , nhưng bạn nên đưa nó vào. Làm như vậy có thể giúp giữ cho mục đích của truy vấn rõ ràng và cải thiện khả năng đọc của nó.

Kết luận

Bằng cách đọc hướng dẫn này, bạn đã học cách sử dụng các phép toán JOIN để kết hợp các bảng riêng biệt thành một tập kết quả truy vấn duy nhất. Mặc dù các lệnh hiển thị ở đây sẽ hoạt động trên hầu hết các database quan hệ, nhưng hãy lưu ý mọi database SQL sử dụng cách triển khai ngôn ngữ duy nhất của riêng nó. Bạn nên tham khảo tài liệu chính thức của DBMS để có mô tả đầy đủ hơn về từng lệnh và bộ tùy chọn đầy đủ của chúng.

Nếu bạn muốn tìm hiểu thêm về cách làm việc với SQL, ta khuyến khích bạn xem các hướng dẫn khác trong loạt bài này về Cách sử dụng SQL .

Tags:

Các tin liên quan