1-8 Cách sử dụng EXISTS
Logic của vị từ trong SQL Có 2 luận lý cơ bản làm nên SQL. Một là những luận tập hợp đã nêu ra chủ yếu cho đến chương này, hai là luận lý của vị từ của luận lý học hiện đại mang tính thông thường. Lần này chúng ta sẽ tiếp cận vào cái cột thứ hai đó. Đặc biệt chúng ta sẽ chú ý vào những đặc ...
Logic của vị từ trong SQL Có 2 luận lý cơ bản làm nên SQL. Một là những luận tập hợp đã nêu ra chủ yếu cho đến chương này, hai là luận lý của vị từ của luận lý học hiện đại mang tính thông thường. Lần này chúng ta sẽ tiếp cận vào cái cột thứ hai đó. Đặc biệt chúng ta sẽ chú ý vào những đặc tính của EXISTS là vị từ quan trọng biểu hiện lượng hóa trong SQL và đào sâu vào lý giải SQL hơn bằng việc giới thiệu những phương pháp ứng dụng của nó.
Có 2 luận lý rất lớn là những luận lý cơ bản hỗ trợ cho cơ sở dữ liệu quan hệ và SQL. Đầu tiên chính là luận về tập hợp, là một lĩnh vực của toán học. Tiếp theo, một cái nữa là vị từ logic của một thể cơ bản của luận lý hiện đại, nói một cách chính xác hơn, hạn định lại nhỏ hơn thì đó chính là Logic vị từ bậc 1. Quyển sách hầu hết đã giải thích từ mặt mang tính chất luận tập hợp trong SQL. Lần này chúng ta sẽ đổi góc nhìn một chút và sẽ học nó theo ý nghĩa của một cái cột khác là logic của các vị từ đã nêu ở trên.
Điều chúng tôi muốn giải thích trọng điểm lần này chính là vị từ EXISTS. EXISTS có thể nhìn một thể nhiều dòng thành một đơn vị và có thể ghi nhiều điều kiện cao độ, không chỉ vậy mà còn rất lợi thế về mặt perfomance không kể nó sử dụng subquery, đây chính là một chức năng không thể không sử dụng trong SQL. Nhưng cái này hoạt động theo nguyên lý nào hay nó được đưa vào với ý đồ gì thì vẫn chưa được lý giải một cách chi tiết.
Nếu viết từ kết luận thì EXISTS được đưa vào SQL để thực hiện chức năng rất mạnh của luận lý vị từ lượng hóa. Bằng việc lý giải khái niệm này rồi sử dụng thành thạo vị từ EXISTS này chính là chúng ta sẽ nâng cấp một bậc sức mạnh như một kĩ sư về cơ sở dữ liệu.
Về dòng chảy của chương này là nửa đầu sẽ tiến hành giải thích một cách đơn giản về logic vị từ, hay chính là EXISTS, và nửa sau sẽ là nêu ra những ứng dụng cụ thể của nó. Với những người cảm thấy dễ hiểu hơn nếu đọc ví dụ trước thì có thể đọc từ phần sau rồi tham chiếu lên phần trước cũng không sao.
Phần lý luận
Vị từ là gì?
Trong những từ mô tả một hoạt động hoặc một tác vụ mà máy tính có thể nhận biết và thực hiện được (Reserved word) thì có rất nhiều những phạm trù được nêu ra. Ví dụ như những vị từ so sánh như =, <, > hay BETWEEN, LIKE, IN, IS, IS NULL. Nhưng cuối cùng thì vị từ là gì? Chắc hẳn không ít người hàng ngày vẫn dùng nó như một điều đương nhiên nhưng nếu bị hỏi thì chưa chắc đã biết được định nghĩa của vị từ đó là gì. Tất nhiên nó khác với chủ ngữ, vị ngữ thông thường trong tiếng Việt và cũng khác với động từ trong tiếng Anh.
Ngay cả cuốn sách này cũng vậy, từ trước đến nay những từ này vẫn được dùng một cách đương nhiên nhưng lần này chúng ta sẽ đi vào tìm hiểu định nghĩa chính xác của những từ này. Nếu nói bằng một từ thì đó là hàm số, nhưng tất nhiên nó khác những hàm số thông thường như SUM hay AVG. Nếu giống thì người ta cũng chả phải mất công tạo thêm một từ nữa là vị từ thay cho hàm số làm gì.
Thực ra vị từ với ý nghĩa là hàm số thì nó cũng có ý nghĩa hàm số nhưng nó là một loại hàm số đặc biệt, có nghĩa là hàm số với kết quả trả về là những kết quả chân lý. Trên thực tế, kết quả trả về của những vị từ được nêu ra ở trên chính là true, false và unknown. (Trong logic vị từ thông thường thì sẽ không có giá trị unknown nhưng cái này đang được sử dụng trong SQL nên sẽ được trả lại 3 loại giá trị).
Nếu hỏi tại sao logic vị từ lại chuẩn bị những dụng cụ như thế này thì là để điều tra cấu trúc của mệnh đề (mọi người hãy nghĩ đây là một câu là được). Ví dụ, chuẩn bị một vị từ là "X là con trai" và nhập X là "Tarou" và "Hanako" thì nó sẽ quyết định tính chân lý của mệnh đề trả lại là "Tarou là con trai" và "Hanako là con trai". Trong logic mệnh đề trước khi vị từ được sinh ra thì đã không tồn tại công cụ nào để điều tra đến tận nội bộ của mệnh đề. Nên vị từ được nêu ra với điểm tiếp cận mang tính hàm số đến phân tích mệnh đề.
Trong cơ sở dữ liệu quan hệ thì một dòng trong bảng được xem như một mệnh đề.
Tbl_A
| name | sex | age |
|---|---|---|
| Tanaka | Nam | 28 |
| Suzuki | Nữ | 21 |
| Yamada | Nam | 32 |
Ví dụ chúng ta có thể nghĩ trong bảng này thì dòng 1 là mệnh đề biểu hiện "Tanaka có giới tính nam, với tuổi là 28". Bảng được hình ảnh như một tập hợp của những dòng nhưng nếu chính ta suy nghĩ theo logic vị từ thì đó là tập hợp của những mệnh đề (tập hợp của câu). Date đã nói điều này như một câu bông đùa là "Cơ sở dữ liệu khác với tên của từ đó, nó không phải tập hợp của những dữ liệu mà chính là tập hợp của những câu văn".
"Điều người ta nói Database (không liên quan gì đến tên của nó) thì trên thực tế nó không phải tập hợp của dữ liệu mà nó là tập hợp của sự thật (nói một cách khác thì đó chính là những mệnh đề đúng) được Codd đưa ra đầu tiên trong mô hình quan hệ năm 1969."
Giống như vậy, WHERE mà chúng ta hay viết cũng được tạo nên như một vị từ kết hợp với một vị từ khác. Chỉ những meenhjd của WHERE mà giá trị trả về là đúng thì mới được chọn từ bảng (tập hợp các mệnh đề).
Các tầng tồn tại
Về cơ bản thì coi như phần định nghĩa về vị từ đến đây là xong, nếu so sánh cách dùng của = hay BETWEEN với EXISTS thì nó có một sự khác nhau khá lớn. Nếu nói về sự khác nhau này trong một câu thì đó chính là điểm argument của vị từ là gì. Argument của vị từ này ví dụ như [x=y] hay [x BETWEEN y AND z] thì đó là một giá trị đơn giản như [13] hay [Yamada], và nó được gọi là giá trị Scalar. Mặt khác, nghĩ về argument của vị từ EXISTS .... thì cuối cùng nó là gì? Nhìn ví dụ dưới đây thì chúng ta không thể nhìn giá trị của argument của EXISTS không thể nhìn ra đó là một giá trị thông thường.
SELECT id
FROM Foo F
WHERE EXISTS
(SELECT *
FROM Bar B
WHERE F.id = B.id);
Tuy nhiên, chúng ta cũng không cần phải quá lo lắng về điều này. Nếu nói về Argument của hàm số SUM( ) thì đó chính là phần nằm trong dấu ngoặc, cũng như vậy thì argument của hàm số EXISTS( ) cũng là phần nằm trong dấu ngoặc. Như vậy, với ví dụ ở trên thì argument của hàm số EXISTS chính là
SELECT * FROM Bar B WHERE F.id = B.id
Đó chính là câu lệnh SELECT, hay nói một cách khác, đó chính là tập hợp những dòng trên. Như vậy, trong danh sách subquery của SELECT trong EXISTS thì chúng ta có 3 cách để viết.
- Wild card: SELECT*
- Định số: SELECT 'Chỗ này thì cái gì cũng được'
- Dãy: SELECT col
Tuy nhiên, tất nhiên trong 3 cách viết này thì kết quả sẽ khác nhau. Trong logic vị từ thì thông quan trình độ nhập này người ta sẽ phân loại những vị từ khác nhau. Những vị từ mà nhập vào là một dòng như = hay BETWEEN thì người ta gọi là "Vị từ bậc một", những vị từ như EXISTS mà nhập vào là tập hợp những dòng thì người ta gọi là "Vị từ bậc hai". Bậc (order) chính là khái niệm để phân biệt tập hợp và trình độ của vị từ. Cũng giống như dưới đây,
Vị từ bậc 3 = Vị từ lấy từ nhập tập hợp của tập hợp Vị từ bậc 4 = Vị từ lấy đầu vào là tập hợp của tập hợp của tập hợp . . .
và nó cứ kéo dài vô hạn, nhưng trong SQL thì không có vị từ nào có bậc 3 trở lên nên mọi người cũng không cần quá chú ý nhiều vào điểm này.
Đối với những người có kinh nghiệm sử dụng Java hay những ngôn ngữ mang tính hàm số như Lisp hay Hakell thì chắc chắc cũng sẽ biết đến khái niệm Hàm số bậc cao rồi đúng không. Nó có nghĩa là lấy hàm số làm đầu vào của hàm số. Bậc trong nghĩa này cũng giống nghĩa của bậc trong logic vị từ. (Nói vậy nhưng định nghĩa về bậc chính là xuất phát từ logic vị từ và luận tập hợp). EXISTS được gọi là vị từ bậc hai là vì nó lấy argument là sự tồn tại bậc 1 của tập hợp, mà hơn nữa vị từ cũng chính là một loại hàm số nên chúng ta có thể nói như sau cũng được.
EXISTS chính là một hàm số bậc cao
Những bậc tồn tại trong cơ sở dữ liệu, có bậc 0 chính là dòng, bậc 1 là một bảng có nhiều dòng, bậc 2 là nhiều bảng có nhiều dòng. Cũng như nếu lên ở phần đầu thì EXISTS được sử dụng trong logic vị từ thì có một phạm vị hơi hẹp. Đó chính là bởi vì EXISTS của SQL chỉ có thể lấy argument đến sự tồn tại bậc 1. Một cách thông thường hơn nữa thì để nó có thể lấy argument đến tồn tại bậc 2, bậc 3 thì SQL cần sự trợ giúp của logic vị từ bậc cao. Đây không phải là việc không thể nhưng nó vẫn chưa được thực hiện.
Trên thực tế thì trong thời điểm năm 1960 lần đầu tiên Codd nghĩ đến mẫu quan hệ thì đã có ý tưởng về ngôn ngữ cơ bản về luận vị từ bậc 2 nhưng những năm 70 thì suy nghĩ này được chỉnh sửa và người ta đã chọn hạn định là luận vị từ bậc 1. Về chi tiết có thể tham khảo trong "Phần 2: Lịch sử của cơ sở dữ liệu quan hệ".
Ví dụ trong tương lai có ngày mà trong SQL hoàn thiện được logic vị từ bậc 2 thì chúng ta có thể lượng hóa đối với bảng. Tức là cũng như Date đã nói, trong SQL hiện này thì ngược với chuyện chúng ta chỉ có thể biểu hiện truy vấn như "Có tồn tại dòng nhà cung cấp S1 không" thì chúng ta có thể hiển thị được truy vấn một bậc cao hơn. Lúc đó thì SQL với tư cách là một ngôn ngữ, sẽ bay lên một tầng cao mới.
Lượng hóa tồn tại và lượng hóa toàn xưng
Những điều như trên nếu nói bằng ngôn ngữ hình thức thì chúng ta có thể hiểu không cần phải có hỗ trợ cho cả hai là EXISTS và FORALL. Tuy nhiên, trên thực tế thì người ta rất cần sự hỗ trợ cho cả hai thứ này. Ví dụ trong SQL thì EXISTS được hỗ trợ nhưng FORALL thì không, thế nên trong SQL tồn tại những truy vấn khó chịu. ---C.J.Date
Trong logic vị từ thì chúng ta có những vị từ đặc biệt là công cụ để viết những câu văn như "Tất cả x đều thỏa mãn điều kiện P" và "Tồn tại (ít nhất 1) x thỏa mãn điều kiện P". Đằng trước được gọi lượng hóa toàn xưng còn đằng sau thì được gọi là lượng hóa tồn tại, nếu theo dấu kí hiệu thì chúng ta có thể kí hiệu ∀ và ∃. Có thể mọi người sẽ nghĩ đây là những dấu khá kì lạ, khi dấu lượng hóa toàn xưng thì chính là chữ A lộn ngược lại, còn dấu lượng hóa tồn tại chính là chữ E được xoay ra.
Có thể có những người nghĩ ra ngay, vị từ EXISTS trong SQL thực hiện dấu lượng hóa tồn tại của logic vị từ. Tuy nhiên một điểm rất quan trọng đó chính là trong SQL thì vị từ đối ứng với một loại lượng hóa còn lại là lượng hóa toàn xưng thì không được đưa vào. Date đã tự ý cho FORALL vào sử dụng trong cuốn sách của mình nhưng trên thực thế thì không có trong SQL.
Tuy nhiên, nếu nói rằng như vây thì khả năng biểu hiện của SQL thì hoàn toàn thiếu thì cũng không hẳn là như vậy. Nếu một trong hai lượng hóa toàn xưng hay lượng hóa tồn tại được định nghĩa ta có thể hoàn toàn dựa vào đó mà định nghĩa cái còn lại. Về chi tiết thì ta có qui tắc định nghĩa như sau để ra được đồng giá trị.
∀xPx = ¬∃x¬Px (Tất cả x đều thỏa mãn điều kiện P = Không tồn tại x nào không thỏa mãn điều kiện P)
∃xPx = ¬∀x¬Px (Tồn tại x thỏa mãn điều kiện P = Không hẳn tất cả mọi x đều phải thỏa mãn điều kiện P)
Như vậy để biểu hiện quan hệ với mọi thì chúng ta có thể dùng câu "Tất cả các dòng đều thỏa mãn điều kiện P" chuyển thành câu "Không có dòng nào không thỏa mãn điều kiện P". Cũng như Date đã nói thì nếu có quan hệ với mọi như vậy thì rất tiện nhưng chỉ những cái đó thì không làm nên cấu trúc của một ngôn ngữ.
Trên đây chúng ta đã giải quyết xong về logic lượng hóa, logic vị từ là những luận lý cơ bản của SQL. Tiếp dưới đây sẽ là những ví dụ cụ thể sử dụng lượng hóa trong SQL.
Chương thực hành
Tìm dữ liệu không tồn tại trong bảng
Bình thường trong trường hợp muốn tìm dữ liệu trong cơ sở dữ liệu thì ta chỉ cần tìm những thứ thỏa mãn điều kiện nào đó có từ trong dữ liệu tồn tại trong bảng, thì đó là những trường hợp thông thường. Tuy nhiên, nếu bây giờ chúng ta rơi vào hoàn cảnh phải tìm những dữ liệu khôn có trong bảng. Ví dụ như ta có trường hợp như dưới đây thì phải làm thế nào?
Giả sử chúng ta có bảng để ghi những người có mặt trong một buổi họp nào đó được diễn ra nhiều lần.
Meetings| meeting | person |
|---|---|
| Lần 1 | Ito |
| Lần 1 | Mizushima |
| Lần 1 | Bando |
| Lần 2 | Ito |
| Lần 2 | Miyada |
| Lần 3 | Bando |
| Lần 3 | Mizushima |
| Lần 3 | Miyada |
Là một chuyện đương nhiên, từ bảng này chúng ta có thể dễ dàng tìm được nhân vật đã tham gia trong những buổi họp này. Nhưng để thống kê những người không tham gia buổi họp thì chúng ta phải làm như thế nào? Ví dụ, Ito-san có mặt trong buổi học thứ nhất và thứ hai nhưng không có mặt trong buổi họp thứ 3. Hay anh Bando vắng mặt trong buổi họp thứ 2. Có nghĩa rằng chúng ta muốn lấy được danh sách những người không có mặt trong buổi họp. Sau khi lấy xong thì kết quả sẽ như sau,
meeting person ------- -------- Lần 1 Miyada Lần 2 Bando Lần 2 Mizushima Lần 3 Ito
Về những dữ lệu tồn tại thì không phải chúng ta thiết lập những điều kiện để làm thỏa mãn một điều kiện nào đó mà bây giờ chúng ta sẽ thiết lập ở một bậc cao hơn đó chính là dữ liệu đó có tồn tại hay không?.Đây chính là "Liên hệ bậc 2". Đối với những trường hợp này thì chính là sự ra trận của EXISTS. Về cách nghĩ thì chúng ta sẽ tạo một tập hợp trường hợp giả định tất cả mọi người cùng tham gia, rồi từ đó sẽ trừ đi tập hợp những người tham gia thực tế của từng buổi là được.
Về tập hợp của trường hợp tất cả mọi người đều tham gia thì chúng ta có thể tạo ra một cách dễ dàng qua
SELECT DISTINCT M1.meeting, M2.member FROM Meetings M1 CROSS JOIN Meetings M2;Trường hợp tất cả mọi người đều tham gia vào tất cả cuộc họp
| meeting | person |
|---|---|
| Lần 1 | Ito |
| Lần 1 | Mizushima |
| Lần 1 | Bando |
| Lần 1 | Miyada |
| Lần 2 | Ito |
| Lần 2 | Mizushima |
| Lần 2 | Bando |
| Lần 2 | Miyada |
| Lần 3 | Ito |
| Lần 3 | Mizushima |
| Lần 3 | Bando |
| Lần 3 | Miyada |
3 lần x 4 người = 12 dòng. Từ đây chúng ta chỉ cần từ đây giới hạn tập hợp không tồn tại tại bảng Meetings là tập hợp những người tham gia trên thực tế nữa là hoàn thành.
--Query chỉ tìm những người không tham gia. Bản 1: Ứng dụng lượng hóa tồn tại
SELECT DISTINCT M1.meeting, M2.member
FROM Meetings M1 CROSS JOIN Meetings M2
WHERE NOT EXISTS
(SELECT *
FROM Meetings M3
WHERE M1.meeting = M3.meeting
AND M2.person = M3.person);
Đây là code dịch trực tiếp điều kiện sang SQL nên ý nghĩa rất rõ ràng. Tuy nhiên chúng ta cũng có thể viết mang tính luận tập hợp và sử dụng phép toán tập hợp giao như dưới đây.
--Query chỉ tìm những người không tham gia. Bản 2: Sử dụng phép toán tập hợp giao SELECT DISTINCT M1.meeting, M2.member FROM Meetings M1 CROSS JOIN Meetings M2 EXCEPT SELECT meeting, person FROM Meetings;
Như vậy chúng ta có thể hiểu là NOT EXISTS mang tính năng giống như phép toán tập hợp giao.
Phép với mọi trong tập hợp. Bản 1: Chuyển đổi khẳng định thành phủ định 2 tầng
Bây giờ chúng ta cùng vào luyện phép với mọi có thể nói là trọng tâm của cách sử dụng vị từ EXISTS. Điều chúng tôi muốn mọi người trang bị cho mình ở đây chính là kĩ thuật chuyển đổi từ biểu hiện khẳng định "Về tất cả những dòng ~" thành "Không tồn tại một dòng nào không ~".
Về ví dụ thì chúng ta sử dụng bảng lưu kết quả thi của học sinh như sau.
TestScores| student_id | subject | score |
|---|---|---|
| 100 | Toán | 100 |
| 100 | Văn | 80 |
| 100 | Lý | 80 |
| 200 | Toán | 80 |
| 200 | Văn | 95 |
| 300 | Toán | 40 |
| 300 | Văn | 90 |
| 300 | Xã hội | 55 |
| 400 | Toán | 80 |
Từ đây chúng ta bắt đầu đi từ vấn đề dễ trước. Hãy chọn những học sinh có tất cả điểm số trên 50. Kết quả sẽ là học sinh có số ID 100, 200, 400. Số 300 qua được trong số điểm của môn Văn và Xã hội nhưng điểm 40 của môn Toán đã làm bạn thất bại. Vậy, Tất cả môn thi đều có điểm trên 50 nếu chuyển đồng giá trị thành câu phủ định 2 lần, thì chúng ta có thể có Không tồn tại một điểm nào dưới 50 Nếu chúng ta chuyển điều kiện này thành NOT EXISTS, thì có thể viết như sau.
SELECT DISTINCT student_id
FROM TestScores TS1
WHERE NOT EXISTS
(SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND TS2.score < 50);
Kết quả
student_id
-----------
100
200
400
Mọi người thấy thế nào? Đơn giản đúng không? Như vậy, tiếp theo chúng ta cùng thử sức để thiết lập nhứng điều kiện phức tạp hơn. Hãy suy nghĩ truy vấn để chọn những học sinh cùng thỏa mãn những điều kiện dưới đây.
- Điểm toán trên 80
- Điểm văn trên 50
Kết quả chắc chắn là những học sinh có số ID 100, 200, 400. Những học sinh như số 400, kể cả không có dữ liệu điểm môn Văn nhưng cũng có bao gồm trong kết quả. Với những yêu cầu như thế này chắc hẳn mọi người cũng đã nhìn thấy nhiều trong những công việc trên thực tế, nhưng chắc hẳn không ít người nhìn qua mà không phát hiện ra đây là điều kiện với mọi.
Tuy nhiên, nếu chúng ta đọc lại như dưới đây thì chắc chắn việc sử dụng lượng hóa với mọi sẽ rõ ràng hơn. Đối với tất cả những dòng của một học sinh, thì nếu là điểm toán thì phải trên 80, nếu là điểm văn thì phải trên 50
Đúng, thực ra đây là lượng hóa để phân kì điều kiện tùy theo những dòng trong tập hợp giống nhau. Trong SQL thì chúng ta có thể biểu hiện sự phân kì điều kiện theo từng dòng như thế này. Để biểu hiện cái này thì chúng ta sử dụng hàm CASE.
CASE WHEN subject = 'Toán' AND score >= 80 THEN 1
WHEN subject = 'Văn' AND score >= 50 THEN 1
ELSE 0 END
Đây chính là hàm số đặc tính đã được nêu ra trong chương "Sức mạnh của câu lệnh HAVING". Đối với những dòng thỏa mãn điều kiện này thì sẽ trả lại kết quả 1, còn không thì sẽ trả lại kết quả 0. Còn lại chúng ta chỉ cần cho điều kiện này lặp đi lặp lại là xong.
SELECT DISTINCT student_id
FROM TestScore TS1
WHERE subject IN ('Toán', 'Văn')
AND NOT EXISTS
(SELECT *
FROM TestScore TS2
WHERE TS2.student_id = TS1.student_id
AND 1 = CASE WHEN subject = 'Toán' AND score >= 80 THEN 1
WHEN subject = 'Văn' AND score >= 50 THEN 1
ELSE 0 END);
Vì những dòng môn khác ngoài toán và văn là không cần nên chúng ta dùng IN để thắt hẹp đối tượng. Sau đó, chúng ta đặt ngược lại điều kiện trong trường hợp toán thì điểm trên 80, trong trường hợp văn thì điểm trên 50 để ghi vào subquery.
Như vậy chúng ta cũng suy nghĩ đến phương pháp xóa trường hợp học sinh số 400 không tồn tại dữ liệu điểm văn. Về điều này thì chúng ta có thể đối ứng với điều này bằng cách sử dụng điều kiện chắc chắn phải có 2 dòng là những môn cần thiết bằng cách thêm câu lệnh HAVING để đếm số dòng.
SELECT student_id
FROM TestScore TS1
WHERE subject IN ('Toán', 'Văn')
AND NOT EXISTS
(SELECT *
FROM TestScore TS2
WHERE TS2.student_id = TS1.student_id
AND 1 = CASE WHEN subject = 'Toán' AND score >= 80 THEN 1
WHEN subject = 'Văn' AND score >= 50 THEN 1
ELSE 0 END)
GROUP BY student_id
HAVING COUNT(*) = 2;
student_id
----------
100
200
Như vậy chúng ta có thể biến đổi đơn cực kì đơn giản là chương trình có thể hoàn thành. Vì sau này chúng ta sẽ GROUP BY student_id nên trong SELECT không cần DISTINCT, thì đây cũng chính là điểm cần chú ý.
Phép với mọi trong tập hợp. Bản 2: Tập hợp vs vị từ -- Cái nào đáng nể hơn?
Chúng ta sẽ luyện tập thêm một chút về lượng hóa toàn xưng. Chúng ta tiến hành thao tác trên trình độ tập hợp chứ không phải đơn chiếc nên EXISTS và HAVING cũng giống nhau ở điểm này. Trên thực tế, cả hai cái này đều có những điểm có thể thay thế cho nhau nên truy vấn nào có thể biểu hiện bằng một cái thì hầu hết đều có thể biểu hiện bằng cái còn lại. Từ đâyc húng ta sẽ tìm ra những ưu điểm và nhược điểm qua so sánh của từng cái một.
Giả sử chúng ta sử dụng bảng quản lý bước làm của một dự án.
Projects| project_id | step_nbr | status |
|---|---|---|
| AA100 | 0 | Hoàn thành |
| AA100 | 1 | Đang chờ |
| AA100 | 2 | Đang chờ |
| B200 | 0 | Đang chờ |
| B200 | 1 | Đang chờ |
| CS300 | 0 | Hoàn thành |
| CS300 | 1 | Hoàn thành |
| CS300 | 2 | Đang chờ |
| DY400 | 0 | Hoàn thành |
| DY400 | 1 | Hoàn thành |
| DY400 | 2 | Hoàn thành |
Key chính ở đây chính là project_id và step_nbr. Số bước thực hiện sẽ bắt đầu từ 0. Chúng ta có thể nghĩ như 0 là định nghĩa yêu cầu, 1 là thiết kế cơ bản... là được. Bảng này thì chỉ có đến số 3 là hết nhưng thực ra cũng có thể có thêm cả những số dưới 4. Tại trạng thái của các bước thì chúng ta có 2 trạng thái chính là nếu xong rồi thì sẽ là "Hoàn thành", còn nếu đang trong trạng thái chờ việc xong của giai đoạn trước thì sẽ là "Đang chờ".
Vâng, và tại đây chính là câu hỏi. Hãy chọn từ bảng những dự án mà đã kết thúc cho đến giai đoạn 1. Đầu tiên, đối với AA100 mới chỉ hoàn thành đến bước 0 và dự án chưa hoàn thành bất cứ bước nào như B200 thì nằm ngoài đối tượng. CS300 đỗ, với DY400 thì đã hoàn thành đến tận bước thứ 2 thì cũng coi như sẽ xóa.
Nếu viết như Celko sử dụng câu lệnh HAVING với một câu trả lời hướng tập hợp thì chúng ta có,
--Chọn dự án hoàn thành đến bước thứ 1: Câu trả lời hướng đối tượng
SELECT project_id
FROM Projects
GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step_tbr <= 1 AND status = 'Hoàn thành' THEN 1
WHEN step_tbr > 1 AND status = 'Đang chờ' THEN 1
ELSE 0 END);
Kết quả
project_id ---------- CS300
Lần này thì việc giải thích HAVING không phải là nội dung chính nên chúng ta chỉ chọn dự án nếu cộng số dòng có số công đoạn không lớn hơn 1 và "Hoàn thành" với số dòng lớn hơn 1 và "Đang chờ" thì được một số bằng số dòng của toàn thể bảng là được.
Mặt khác, nếu giải câu hỏi này theo luận lý vị từ thì sẽ trở nên như thế nào? Thực ra đây có thể suy nghĩ như một bước của lượng hóa toàn xưng, so với câu hỏi trước thì có chút phức tạp hơn nhưng về cách suy nghĩ là giống nhau. Hãy nghĩ rằng chúng ta ghi mệnh đề toàn xưng sau.
Về tất cả những dòng có trong dự án, chọn những dòng có số tiến độ không quá 1 và nếu lớn hơn 1 thì là tình trạng "Đang đợi"
Cả bộ điều kiện này thì chúng ta sẽ sử dụng hàm CASE để biểu diễn.
step_status = CASE WHEN step_nbr <= 1
THEN 'Hoàn thành'
ELSE 'Đang chờ' END
Chúng ta sẽ để thể phủ định này vào phần cuối nên câu trả lời có thể viết như sau.
SELECT *
FROM Projects P1
WHERE NOT EXISTS
(SELECT status
FROM Projects P2
WHERE P1.project_id = P2.project_id
AND status <> CASE WHEN step_nbr <= 1
THEN 'Hoàn thành'
ELSE 'Đang chờ' END);
Kết quả
project_id step_nbr status ---------- --------- ---------- CS300 0 Hoàn thành CS300 1 Hoàn thành CS300 2 Đang chờ CS300 3 Đang chờ
Kể cả biểu hiện những điều kiện giống nhau nhưng về phần sử dụng phủ định 2 lần thì so với HAVING về mặt trực quan thì NOT EXISTS có vẻ khó hiểu hơn chính là khuyết điểm của nó. Tuy nhiên, cách viết này cũng có ưu điểm của nó. Đầu tiên chính là pergomance của nó rất tốt. Ngay cả một dòng nếu có tồn tại dòng không thỏa mãn điều kiện thì sự điều tra về dòng đó sẽ kết thúc ngay nên cũng không phải nhìn tất cả các dòng. Mà hơn nữa thì chúng ta có thể dùng index của project_id nên tốc độ cũng có thể được cải thiện đáng kể. Và điểm thứ 2 đó chính là lượng dữ liệu được bao gồm trong kết quả nhiều. Nếu ta sử dụng HAVING thì chúng ta chỉ biết được project_id thôi nhưng nếu là NOT EXISTS thì chúng ta có thể có cụ thể những thành tố có trong tập hợp.
Lượng hóa đối với dãy: Tìm dòng tất cả là 1
Trong thiết kế bảng không tốt thì có tồn tại những bước cổ điển. Ví dụ như với những bảng có dòng trùng không có key chính. Đối với những trường hợp đó thì đối với những kĩ sư DB như chúng ta thì sẽ lại ngán ngẩm thở dài "Lại như vậy à...", nhưng giả sử có một bảng được làm nên như cop y hệt một dãy như sau.
ArrayTbl|key|col1|col2|col3|col4|col5|col6|Col7|col8|col9|col10| |--------|--------|--------|--------|--------|--------|--------|--------|--------|--------| |A| | | | | | | | | | | |B|3| | | | | | | | | | |C|1|1|1|1|1|1|1|1|1|1| |D| | |9| | | | | | | | |E| |3| |1|9| | |9| | |
Nhưng nếu hỏi tại sao cấu trúc của bảng này không tốt thì dãy này là không có tính mềm dẻo khi thêm hay giảm số thành tố. Chỉ cần thêm hay bớt một dòng thì không tốn ít công sức. Sự thêm hay bớt dòng không có ảnh hưởng gì đến hệ thống cả (Tất nhiên sẽ có ảnh hưởng đối về perfomance nhưng câu chuyện lần này chúng ta chỉ nói về mặt logic). Từ việc này đối với việc thiết kế bảng trong cơ sở dữ liệu thì có một nguyên tắc dẫn đường đó chính là cần phải nghĩ đến cấu trúc mang tính kéo dài ở một mức độ nào đó. Thành tố của dãy chúng ta nên nghĩ trong bảng của cơ sở dữ liệu thì đó không phải là sự đảm nhiệm của dãy mà là đảm nhiệm của hàng thì sẽ tốt hơn.
Nếu tự biết logic của mô hình quan hệ tồn tại của bảng là biểu thị thực thể của thế giới hiện thực (NTT) thì suy nghĩ đó sẽ tự nhiên có được. Nếu chỉ là bảng như ở trên thì dùng dạng dãy của SQL-99 để tạo thì cũng là một cách hơi quá tay.
Tuy nhiên nói như vậy nhưng không có nghĩa cấu tạo của bảng trước mắt sẽ đẹp ra. Nếu không có đất để cải biến bảng thì việc suy nghĩ cách để cải thiện nó như một tiên đề thì sẽ mang tính xây dựng hơn.
Những yêu cầu hay phát sinh khi sử dụng những bảng như trên thì sẽ có những hình thái cơ bản như sau.
- Tìm dòng all 1
- Tìm dòng ít nhất cũng có 1 thành tố là 9
Khác với vị từ EXISTS sẽ dùng để lượng hóa hướng dòng thì đây là lượng hóa hướng đến dãy. Như vậy chúng ta không thể dùng EXISTS ở đây. Như vậy thì chúng ta có thể có suy nghĩ về câu trả lời vô văn hóa (tất nhiên nếu nói là sai thì nó không sai)như dưới đây,
SELECT * FROM ArrayTbl WHERE col1 = 1 AND col2 = 1 . . . AND col10 = 1;
Nếu 10 dòng thì còn có thể nhưng nếu là 50, 100 dòng thì đó có thể sẽ là dung lượng mà SQL không thể đọc được nữa. Tuy nhiên, chuyện này cũng không phải quá lo. SQL vẫn có vị từ để lượng hóa hướng dãy để đối ứng.
SELECT * FROM ArrayTbl1 WHERE 1 = ALL(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
key col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 --- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- C 1 1 1 1 1 1 1 1 1 1
Chúng ta đã dịch trực tiếp sang SQL câu lượng hóa toàn xưng "Tất cả các dòng từ col1~col10 là 1". Đây là một code sạch sẽ và khá dễ đọc.
Đối ngược với nó thì để biểu hiện lượng hóa tồn tại "Ít nhất thì có một giá trị 9" thì chúng ta dùng ANY thay thế cho ALL.
SELECT * FROM ArrayTbl1 WHERE 9 = ANY(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
key col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 --- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- D 9 E 3 1 9 9
Hoặc chúng ta có thể dùng IN thay thế cũng không sao.
SELECT * FROM ArrayTbl1 WHERE 9 IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
IN thì bình thường chúng ta có cách dùng bên trái là dãy và bên phải là số như [col IN(1, 2, 3)] nhưng lần này chúng ta dùng ngược lại nên chắc chắn có người sẽ có cảm giác khác lạ nhưng đây là cách cấu trúc khác thôi.
Nhưng với trường hợp chúng ta muốn dùng NULL chứ không phải là giá trị để làm điều kiện thì với cách làm như cũ thì sẽ không đọc được.
--Tìm dòng all NULL: câu trả lời sai SELECT * FROM ArrayTbl1 WHERE NULL = ALL(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);
Không kể đến nội dung có trong bảng thì chắc chắn kết quả sẽ là rỗng. Nếu chúng ta nghĩ ALL ở đây với ý nghĩa [col1 = NULL, col2 = NULL, ...] thì sẽ rõ ràng hơn. Trong trường hợp này thì chúng ta dùng COALESCE.
--Tìm dòng all NULL: câu trả lời đúng SELECT * FROM ArrayTbl1 WHERE COALESCE(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) IS NULL;
key col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 --- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- A
Tóm tắt
SQL nhìn từ điểm nhìn của luận tập hợp thì nó có sức mạnh phù hợp với tên gọi của nó là ngôn ngữ hướng tập hợp. Nhưng hướng khác nếu ta nhìn từ hướng luận lý vị từ thì nó cho xem một sức mạnh như một dạng ngôn ngữ hàm số.
Cũng như trách nhiệm của hàm số cấp cao đối với ngôn ngữ loại hàm số thì đối với SQL thì EXISTS cũng mang một ý nghĩa quan trọng như vậy. Nếu dùng quen được EXISTS thì chúng ta cũng có thể nói đã phá được quan môn của trung cấp. Phần tiếp theo của chương, chúng tôi sẽ chuẩn bị nhiều ví dụ của EXISTS nên nhất định cách bạn hãy phát huy những điều mình đã học được để thử sức với nó nhé.
Tiếp theo đây là những điểm chính của chương này.
- Vị từ đối với SQL thì chính là hàm số trả lại những giá trị chân lý.
- Chỉ có EXISTS khác với những vị từ khác, lấy argument là tập hợp.
- Điểm đó thì EXISTS được xem như một hàm số bậc cao.
- Trong SQL không có dấu phép toán nào đảm nhiệm lượng hóa toàn xưng nên chúng ta thay thế bằng NOT EXISTS.
Về những tư liệu liên quan đến vị từ EXISTS thì mọi người có thể tham khảo những tài liệu tham khảo dưới đây.
- Joe Celko "SQL Puzzle Tái bản lần 2"(2007)
Những ví dụ ứng dụng EXISTS và NOT EXISTS được nêu ra rất nhiều. Ví dụ như "Puzzle 18. Dirrect mail", hiển thị NOT EXISTS về lượng hóa toàn xưng thì có trong "Puzzle 20. Kết quả bài kiểm tra" và "Puzzle 21. Phép chia quan hệ", ứng dụng đối với phép toán tập hợp chia thì có thể tham khảo ứng dụng tại "Puzzle 57. Tìm số khuyết version 1".
