1-5 Cách sử dụng OUTER JOIN
Nhược điểm của SQL: Xu hướng và đối sách giải quyết. Không thể chỉnh hình kết quả của SQL theo hình mà ta mong muốn là một trong những vấn đề khó xử của những kĩ sư DB phải đối diện. SQL từ đầu không phải ngôn ngữ được thiết kế lên vì mục đích đó nên để chỉnh được format thì chúng ta cần một ...
Nhược điểm của SQL: Xu hướng và đối sách giải quyết. Không thể chỉnh hình kết quả của SQL theo hình mà ta mong muốn là một trong những vấn đề khó xử của những kĩ sư DB phải đối diện. SQL từ đầu không phải ngôn ngữ được thiết kế lên vì mục đích đó nên để chỉnh được format thì chúng ta cần một chút công phu. Chương này sẽ giải thích cách làm biến đổi dãy mang tính đại biểu của chỉnh hình format và đào sâu vào giải thích OUTER JOIN để đạt được chức năng quan trọng trong trường hợp đó.
Mở đầu
Một trong những hiểu lầm dễ có đối với SQL đó chính là "SQL là một ngôn ngữ để tạo phiếu, bảng". Đúng là đang được sử dụng trong hệ thống tạo nên những bảng thống kê, phiếu thông tin khác nhau không kể định hình hay không định hình. Nhưng cho đến cùng thì SQL là ngôn ngữ được tao ra với mục đích tìm kiếm thông tin.
Tuy nhiên đồng thời với đó thì SQL mạnh hơn rất nhiều so với những gì nhiều người nghĩ. Đặc biệt là những năm gần đây nó đã được trang bị thêm rất nhiều chức năng liên quan đến việc tạo báo các mà đại biểu mà hàm OLAP. Có thể giản lược code nguồn trên toàn bộ hệ thống, nếu có thể cung cấp đầy đủ perfomance thì giá trị sử dụng sức mạnh của SQL chắc chắn cũng sẽ trở nên đầy đủ.
Tại chương này sẽ có giới thiệu phương pháp formatting sử dụng OUTER JOIN. OUTER JOIN là một phép tính chắc chắn đã thân thuộc với các kĩ sư DB nhưng lần này chúng ta sẽ tiếp cận những đặc tính đó từ một góc độ hơi khác. Nửa đầu chương sẽ là chỉnh hình format dựa theo OUTER JOIN, còn nửa sau sẽ là nội dung nhìn OUTER JOIN từ điểm nhìn của phép tính tập hợp.
Trong trường hợp mình chưa biết gì về OUTER JOIN thì đọc từ phần sau là "OUTER JOIN hoàn toàn" (FULL OUTER JOIN) và phép tính tập hợp bằng OUTER JOIN thì có lẽ sẽ dễ hiểu hơn.
Biến đổi dãy hàng bằng OUTER JOIN - Phần 1 (hàng -> dãy):Tạo bảng Cross
Phương pháp tạo bảng Cross từ kết quả của truy vấn đã được giới thiệu trong p.8. Lần này chúng ta cũng làm một công việc tương tự nhưng sẽ thực hiện bằng ý tưởng của OUTER JOIN. Về ví dụ thì chúng ta sẽ suy nghĩ bảng để quản lý những khóa học nghiên cứu mà nhân viên đã tham gia.
Courses| name | course |
|---|---|
| Akai | Nhập môn SQL |
| Akai | UNIX cơ bản |
| Suzuki | Nhập môn SQL |
| Kudo | Nhập môn SQL |
| Kudo | Trung cấp Java |
| Yoshida | UNIX cơ bản |
| Watanabe | Nhập môn SQL |
Vấn đề đầu tiên là từ bảng này chúng ta sẽ tạo ra bảng như dưới đây. Nếu là ○ có nghĩa là đã tham gia còn NULL có nghĩa là chưa tham gia.
| |Nhập môn SQL|UNIX cơ bản|Java trung cấp| |--------|--------| |Akai| ○ | ○ | | |Kudo| ○ | | ○ | |Suzuki| ○ | | | |Yoshida| | ○ | | |Watanabe| ○ | | |
Thực ra, bảng này so với bảng ban đầu thì không có sự hơn kém nào về lượng thông tin. Về thông tin ai đã tham gia vào lớp học nào thì từ bảng nào chúng ta cũng có thể biết được. Chẳng qua cách nhìn là khác nhau. Thế nên đây thực sự không phải công việc của SQL. Mặc dù không phải nhưng làm bằng SQL chính là chủ đề chính của chương này. Nếu suy nghĩ theo hướng của OUTER JOIN thì chúng ta có thể có được kết quả bằng cách cố định tên của người tham gia rồi thực hiện OUTER JOIN.
--Triển khai yêu cầu tạo bảng Cross - Phần 1: Sử dụng OUTER JOIN
SELECT C0.name
CASE WHEN C1.name IS NOT NULL THEN '○' ELSE NULL END AS "Nhập môn SQL",
CASE WHEN C2.name IS NOT NULL THEN '○' ELSE NULL END AS "UNIX cơ bản",
CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java trung cấp",
FROM (SELECT DISTINCT name FROM Courses) C0
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'Nhập môn SQL',)C1
ON C0.name = C1.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'UNIX cơ bản',)C2
ON C0.name = C2.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'Java trung cấp',)C3
ON C0.name = C3.name;
Sử dụng Subquery, chúng ta có thể tạo 4 tập hợp C0~C3 từ bảng "Courses". Chúng ta có thể lưu chúng như những tập hợp bình đẳng với nhau về cả bảng lẫn view khi đưa cho chúng những cái tên từ trong SQL. Như vậy có nghĩa là trong trường hợp này thì chúng ta tạo ra 4 tập hợp.
C0:Master C1:SQL C2:UNIX C3:Java
Tất cả những người tham gia khóa học được để vào C0, như vậy ở đây chúng phát huy công việc là "Master người tham gia khóa học".(Những thông tin này nếu được tạo ra ngay từ đầu như một bảng thì chúng ta sử dụng bảng đó luôn). Tiếp theo, để C0 là master và C1~C3 theo thứ tự là những OUTER JOIN. Như vậy những khóa học đã tham gia sẽ hiện lên tên người tham gia và trong dãy khóa học chưa được tham gia sẽ là NULL. Tiếp theo thì chúng ta chỉ cần thay đổi ○ đối với những khóa học đã tham gia bằng hàm CASE là hoàn thành.
Lần này, trên đầu kết quả yêu cầu chỉ có 3 dãy nên đã có 3 lần được tổ hợp. Trong trường hợp dãy có tăng lên thì nguyên lý hoạt động vẫn là giống nhau nên việc đối ứng khi thêm tổ hợp là hoàn toàn có thể. Ngay trong trường hợp ta muốn đổi nội dung cột và dòng đầu thì cũng có thể làm được một cách đơn giản. Phương pháp này có ưu điểm là ý tưởng rất rõ ràng, các động tác trong SQL rất dễ hiểu nhưng có nhược điểm là inline view và tổ hợp nhiều nên dòng code dài hơn ra rất nhiều. Kéo theo dãy dòng đầu dài ra thì perfomance cũng sẽ ác hóa theo.
Tại đây chúng ta thử suy nghĩ một phương pháp đối phó với vấn đề này. Thông thường thì chúng ta có thể thay thế OUTER JOIN bằng Scalar Subquery nên chúng ta cũng có thể nghĩ đến query như sau.
--Triển khai sử dụng scalar subquery
SELECT C0.name
(SELECT '○'
FROM Courses C1
WHERE course = 'Nhập môn SQL'
AND C1.name = C0.name) AS "Nhập môn SQL",
(SELECT '○'
FROM Courses C2
WHERE course = 'UNIX cơ bản'
AND C2.name = C0.name) AS "UNIX cơ bản",
(SELECT '○'
FROM Courses C3
WHERE course = 'Java trung cấp'
AND C3.name = C0.name) AS "Java trung cấp",
FROM (SELECT DISTINCT name FROM Coureses) C0;
Điểm nhấn mạnh ở đây chính là tên của 3 cột được tọa nên bằng Scalar Subquery. Dòng cuối cùng là sử dụng câu lệnh FROM đối với tập hợp C0 là một thứ giống như "Master người tham gia khóa học" chúng ta tạo nên lúc nãy. Scalar Subquery trả lại kết quả nếu điều kiện tổ hợp đồng nhất với nhau thì là '○' còn nếu không thì trả lại kết quả NULL. Điểm lợi của phương pháp này đó chính là ngay cả khi số khóa học có tăng lên hay giảm đi thì chúng ta chỉ cần thay đổi mỗi khu vực SELECT thôi là được nên điểm mạnh của nó có thể nói là chỉnh sửa dễ dàng.
Ví dụ như ta muốn thêm một khóa học nữa là "Nhập môn PHP" vào dòng đầu thứ 4 thì chúng ta chỉ cần thêm một câu lệnh SELECT như sau:
(SELECT'○'
FROM Course C4
WHERE course = 'Nhập môn PHP'
AND C4.name = C0.name) AS "Nhập môn PHP"
(Đối với phương pháp OUTER JOIN thì chúng ta phải sửa 2 chỗ là câu lệnh SELECT và câu lệnh FROM). Đây là một thủ thuật không chỉ đối với những trường hợp phải thay đổi nội dung mà là thủ thuật rất tiện lợi đối với những hệ thống xây dựng SQL động. Đối với tình hình hiện tại thì việc sử dụng scalar subquery bẳng câu lệnh SELECT (hơn nữa là subquery tương quan) đã là một phương pháp cao cấp rồi.
Mục giới thiệu thứ 3 ở đây đó chính là phương pháp sử dụng hàm CASE. Trong hàm CASE có thể viết trong hay ngoài của những hàm số tập hợp bằng câu lệnh SELECT. (Tham khảo P.8). Tại đây một lần nữa hàm SUM sẽ trả lại kết quả NULL hoặc 1, còn hàm CASE bên ngoài sẽ đổi 1 thành '○'.
--Dùng hàm CASE
SELECT name,
CASE WHEN SUM(CASE WHEN course = 'Nhập môn SQL' THEN 1 ELSE NULL END) = 1
THEN'○' ELSE NULL END AS "Nhập môn SQL",
CASE WHEN SUM(CASE WHEN course = 'UNIX cơ bản' THEN 1 ELSE NULL END) = 1
THEN'○' ELSE NULL END AS "UNIX cơ bản",
CASE WHEN SUM(CASE WHEN course = 'Java trung cấp' THEN 1 ELSE NULL END) = 1
THEN'○' ELSE NULL END AS "Java trung cấp",
FROM Coures
GROUP BY name;
Nếu lấy kết quả mà không GROUP BY thì sẽ chỉ có bảng hiện ra thôi nên chúng ta phải GROUP BY với tên của những người tham gia khóa học. Phuowng pháp này so với viết scalar subquery cũng không thua kém gì về độ đẹp trong cách viết, và là một cách viết mạnh đối với những trường hợp nội dung thay đổi. Để quen với cách viết ghép điều kiện với kết quả trả lại của hàm số tập hợp như SUM có lẽ sẽ có một chút khó khăn. Còn về cách nghĩ thì chúng ta có thể suy nghĩ như sau "Ở câu lệnh SELECT thì hàm số tập hợp hay giá trị scalar đều được đánh giá nên nó được đưa vào như một dãy hay một hằng số".
Chuyển đổi dãy dòng bằng OUTER JOIN. Phần 2(dãy->dòng): Tóm tắt những mục lặp lại thành một dãy
Lần trước chúng ta đã chuyển từ dòng thành cột. Nếu vậy lần này chắc chắn sẽ có người muốn chuyển cột thành dòng. Ví dụ chúng ta nghĩ đến một bảng như sau làm cho các DB phải khóc.
Personnel:Thông tin con cái của nhân viên trong công ty|employee|child_1|child_2|child_3| |--------|--------| |Akai|Ichiro|Jiro|Saburo| |Kudo|Haruko|Natsuko| | |Suzuki|Natsuko| | | |Yoshida| | | |
Có thể mọi người đã nhìn qua bảng này một lần rồi đúng không. Trong trường hợp COBOL được sử dụng để làm flat file thành dữ liệu nhập thì khi kéo ra từ format một cách khéo léo thì chúng ta có bảng như trên. Bảng này có vấn đề gì làm kĩ sư DB khóc thì bây giờ chúng ta chưa chạm vào. Cơ bảng là chúng ta chuyển bảng này sang hình dạng cột mang. Sử dụng UNION ALL.
--Chuyển cột thành dòng: Sử dụng UNION ALL SELECT emplyee, child_1 AS child FROM Personnel UNION ALL SELECT emplyee, child_2 AS child FROM Personnel UNION ALL SELECT emplyee, child_3 AS child FROM Personnel;Kết quả
emloyee child ------- ------ Akai Ichiro Akai Jiro Akai Saburo Kudo Haruko Kudo Natsuko Kudo Suzuki Natsuko Suzuki Suzuki Yoshida Yoshida Yoshida
UNION ALL không xóa đi những dòng trùng lặp nên ngay cả một người không có con như Yoshida không được xóa đi mà vẫn được hiện 3 dòng. Như vậy chúng ta chỉ cần làm thành chương trình xóa đi những dòng NULL đối với cột child khi nhập bảng.
Tiếp theo, trong trường hợp muốn có một danh sách như dưới đây vẫn còn giữ lại Yoshida-san.
List con cái của nhân viên trong công ty| employee | child |
|---|---|
| Akai | Ichiro |
| Akai | Jiro |
| Akai | Saburo |
| Kudo | Haruko |
| Kudo | Natsuko |
| Suzuki | Natsuko |
| Yoshida |
Trường hợp này chúng ta không thể chỉ đơn giản xóa đi những dòng mà cột child là NULL. Về phương pháp thì chúng ta có thể nghĩ ra vài phương pháp, đầu tiên chúng ta tạo một cột cố định trẻ con (master cột child).
CREATE VIEW Children(child) AS SELECT child_1 FROM Personnel UNION SELECT child_2 FROM Personnel UNION SELECT chils_3 FROM Personnel;
child ------ Ichiro Jiro Saburo Haruko Natsuko
Như ví dụ thì master được chuẩn bị trong bảng từ đầu tiên nên chúng ta có thể dùng luôn.
Tiếp theo chúng ta sẽ lấy cột nhân viên OUTER JOIN vào master trước. Các bạn hãy chú ý điều kiện tổ hợp.
--SQL có được list con cái của nhân viên công ty (Hiện cả những nhân viên không có con)
SELECT EMP.employee, CHILDREN.child
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);
Tại đây chúng ta đã OUTER JOIN cột nhân viên với master child, nhưng điều quan trọng là điều kiện JOIN mà được chỉ định bằng IN. Theo như cách này nếu như 3 cột từ child1~child3 ở bảng Personnel có kết quả trùng với tên trong view Children đã lập thì sẽ trả lại kết quả là tên đó, nếu không trùng thì sẽ trả lại kết quả NULL. Nhà Kudo và nhà Suzuki cùng có con tên là "Natsuko" nhưng trong trường hợp này thì chương trình vẫn chạy đúng là đưa lại kết quả như ta mong muốn.
Tạo bảng Cross
Trong nghiệp vụ tạo bảng thống kê thì chắc chắn sẽ có trường hợp phát sinh mong muốn cột tiêu đề và dòng tiêu đề lồng nhau. Ví dụ trong trường hợp chúng ta tạo bảng Cross như sau lấy thông tin từ bảng lưu giứ thông tin dân số tùy theo giới tính, các tầng lớp tuổi và tỉnh "TblPop".
Master tầng tuổi: TblAge| age_class | age_range |
|---|---|
| 1 | 21~30 tuổi |
| 2 | 31~40 tuổi |
| 3 | 41~50 tuổi |
| sex_cd | sex |
|---|---|
| m | Nam |
| f | Nữ |
|pref_name|age_class|sex_cd|population| |--------|--------| |Akita|1|m|400| |Akita|3|m|1000| |Akita|1|f|800| |Akita|3|f|1000| |Aomori|1|m|700| |Aomori|1|f|500| |Aomori|3|f|800| |Tokyo|1|m|900| |Tokyo|1|f|1500| |Tokyo|3|f|1200| |Chiba|1|m|900| |Chiba|1|f|1000| |Chiba|3|f|900|
Bảng thống kê khi dòng là dòng lồng
Trọng điểm của vấn đề này ở đây là trong bảng TblPop không có dữ liệu nào của cấp tuổi 2 nhưng trong kết quả thì kết quả đó vấn được ghi vào và xuất hiện 1 bảng cố định với 6 dòng. Nếu là cố định cột bên thì đó chính là sự xuất hiện của OUTER JOIN nhưng chúng ta cần có dòng lồng trong đó. Lần này chúng ta sẽ sẽ làm các cấp tuổi và giới tính sẽ là cột dựng nên chúng ta sử dụng master với "TblSex" và "TblAge".
Với cách suy nghĩ thông thường thì chúng ta sẽ master 2 bảng này rồi OUTER JOIN nhưng nếu chỉ dùng lặp lại những OUTER JOIN thông thường thì chương trình sẽ không chạy như mong muốn.
--Tạo bảng với lồng dòng bằng OUTER JOIN: SQL sai
SELECT
MASTER1.age_class AS age_class,
MASTER2.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM
(SELECT
age_class,
sex_cd,
SUM(CASE WHEN pref_name IN ('Aomori', 'Akita')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('Tokyo', 'Chiba')
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN TblAge MASTER1
--OUTER JOIN 1: Tổ hợp với Master tầng tuổi
ON MASTER1.age_class = DATA.age_class
RIGHT OUTER JOIN TblSex MASTER2
--OUTER JOIN 2: Tổ hợp với Master giới tính
ON MASTER2.sex_cd = DATA.sex_cd;
Kết quả
age_class sex_cd pop_tohoku pop_kanto ----------- -------- ------------ ----------- 1 m 1100 1800 1 f 1300 2500 3 m 1000 3 f 1800 2100
Cũng như những gì đã nhìn ở kết quả thì dòng lớp tuổi "2" không hiện ra. Cái này có vẻ không được, chúng ta đã mất công dùng OUTER JOIN nhưng tại sao chương trình lại không thực hiện theo ý mình?
Đó là vì trong TblPop không có dòng nào có lớp tuổi "2" được ghi lại... Ơ nhưng không phải OUTER JOIN là kĩ thuật mà chúng ta cũng có thể có được kết quả định hình như vậy khi sử dụng sao?
Đúng, đúng là có chuyện đó. Bằng chứng là thời điểm kết thúc tổ hợp master của các lớp tuổi thì kết quả vẫn còn ghi lại kết quả "2".
--Trường hợp dừng lại ở OUTER JOIN ban đầu: trong kết quả cũng xuất hiện kết quả "2" của các lớp tuổi.
SELECT
MASTER1.age_class AS age_class,
DATA.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM
(SELECT
age_class,
sex_cd,
SUM(CASE WHEN pref_name IN ('Aomori', 'Akita')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('Tokyo', 'Chiba')
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN TblAge MASTER1
ON MASTER1.age_class = DATA.age_class;
Kết quả
age_class sex_cd pop_tohoku pop_kanto ----------- -------- ------------ ----------- 1 m 1100 1800 1 f 1300 2500 2 --Vẫn còn tồn tại lớp tuổi 2 3 m 1000 3 f 1800 2100
Vấn đề ở đây chính là tuy nhiên. Đây là chỗ khá trọng tâm nên các bạn nên chú ý, việc chúng ta có được dòng lớp tuổi "2" từ bảng TblAge. Tuy nhiên, đối với kết quả đó trong bảng giới tính sẽ là kết quả NULL. Vấn đề này nếu chúng ta nghĩ một cách cẩn thận thì đó là đương nhiên, trong bảng TblPop không có dòng dữ liệu về lớp tuổi "2" nên tất nhiên cột giới tính bên đó cũng được trả lại kết quả NULL. Tiếp theo đối với tổ hợp master giới tính thì điều kiện tổ hợp sẽ trở thành [ON MASTER2.sex_cd = NULL]. Kết quả là unknown (Tham khảo luận lý 3 giá trị). Vậy nên giá trị cuối cùng sẽ không bao giờ hiện ra kết quả của lớp tuổi "2". Và thứ tự của bảng tổ hợp cũng không được thực hiện một cách thứ tự.
Vậy ở đây chúng ta làm thế nào để có dòng lồng một cách chính xác? Đó chính là
Nếu OUTER JOIN 2 lần không được cho phép thì chúng ta sẽ hoàn thành trong vòng 1 lần là được.
--Tạo bảng có dòng lồng bằng OUTER JOIN: SQL đúng
SELECT
MASTER.age_class AS age_class,
MASTER.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM
(SELECT
age_class,
sex_cd,
SUM(CASE WHEN pref_name IN ('Aomori', 'Akita')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('Tokyo', 'Chiba')
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN
(SELECT age_class, sex_cd
FROM TblAge
CROSS JOIN
TblSex ) MASTER
ON MASTER.age_class = DATA.age_class
AND MASTER.sex_cd = DATA.sex_cd;
Kết quả
age_class sex_cd pop_tohoku pop_kanto ----------- -------- ------------ ----------- 1 m 1100 1800 1 f 1300 2500 2 m 2 f 3 m 1000 3 f 1800 2100
Ở đây chúng ta có chính xác được 6 dòng. Kể cả TblPop có là bảng không hoàn thành như thế nào đi chăng nữa nhưng ở đây vẫn xuất hiện bảng với 6 hàng cố định. Thủ thuật ở đây chính là tổ hợp Cross 2 bảng là "TblAge" và "TblSex", và chúng ta sẽ có được kết qủa là MASTER. Số dòng sẽ là 3 x 2 = 5
MASTER| age_class | sex)cs |
|---|---|
| 1 | m |
| 1 | f |
| 2 | m |
| 2 | f |
| 3 | f |
| 3 | f |
Như vậy việc OUTER JOIN có thể hoàn thành trong 1 lần đối với view MASTER này. Có nghĩa là khi chèn lồng dòng vào bên cột đứng thì chúng ta đã chuẩn bị sẵn hình dạng của master rồi. Trong trường hợp lồng 3 dòng thì thao tác hoàn toàn tương tự.
Trong trường hợp DB không có câu lệnh CROSS JOIN thì chúng ta có thể làm như "FROM TblAge, TblSex" để có thể có những phép tính và tổ hợp tương tự mà không cần chỉ định điều kiện tổ hợp, chỉ cần sắp xếp bảng.
Tổ hợp phép nhân
Trong phần "Sức mạnh của câu lệnh HAVING", tại phần phép chia quan hệ chúng ta cũng đã đề cập đến phần "Trong SQL, tổ hợp là công việc của phép nhân". Tại đây chúng ta có master sản phẩm và bảng quản lý lịch sử bán hàng của sản phẩm, chúng ta sẽ lấy 2 bảng này làm vó dụ để đào sâu.
Items| item_no | item |
|---|---|
| 10 | FD |
| 20 | CD-R |
| 30 | MO |
| 40 | DVD |
|sale_date|item_no|quantily| |--------|--------| |2007-10-01|10|4| |2007-10-01|20|10| |2007-10-01|30|3| |2007-10-03|10|32| |2007-10-03|30|12| |2007-10-04|20|22| |2007-10-04|30|7|
Bây giờ chúng ta sử dụng 2 bảng trên để xuất ra phiếu điều tra tổng thống kê mặt hàng nào bán được bao nhiêu theo tùy từng mặt hàng
item_no total_qty
------- ----------
10 36
20 32
30 22
40
Vì có cả kết quả của mặt hàng số 40 không tồn tại trong lịch sử bán hàng (=không bán được cái nào) nên chắc chắn đây là kết quả của sự có mặt của OUTER JOIN, việc sử dụng OUTER JOIN là chắc chắn. Như vậy chắc sẽ có nhiều người suy nghĩ đến câu trả lời như dưới đây.
--Câu trả lời 1: Bằng việc tính toán tập hợp trước khi tổ hợp nên tạo ra mối quan hệ 1 đối 1
SELECT I.item_no, SH.total_qty
FROM Items I LEFT OUTER JOIN
(SELECT item_no, SUM(quantity) AS total_qty
FROM SalesHistory
GROUP BY item_no) SH
ON I.item_no = SH.item_no
Đây cũng có thể nói là một đáp án đúng, có một ý đồ rất dễ hiểu. Đầu tiên trước khi tổ hợp thì ta tập hợp bảng lịch sử bán hàng bằng số hiệu sản phẩm, theo đó sẽ tạo được key chính item_no thành view giữa như dưới đây.
View giữa nhất ý với số hiệu sản phẩm| item_no | total_qty |
|---|---|
| 10 | 36 |
| 20 | 32 |
| 30 | 22 |
Tiếp theo thì nếu tổ hợp bằng item_no giữa master sản phẩm và view này là được. Nhìn qua thì đây là một truy vấn với tầm nhìn tốt.
Tuy nhiên, nếu nhìn từ tầm nhìn là perfomance thì truy vấn này vẫn còn tồn tại những vấn đề. Chúng ta phải một lần lưu trên bộ nhớ dữ liệu của SH là view trung gian. đã mất công nhất ý bằng item_no thì vì không tồn tại chỉ số của key chính trong chính SH thì không thể sử dụng được trong điều kiện tổ hợp.
Chìa khóa để cải thiện truy vấn này chính là thêm điểm nhìn "Nhìn dưới góc độ phép nhân tổ hợp". Hiện nay, đúng là view SH với master sản phẩm Items đã có mối quan hệ 1 với 1. Tuy nhiên thực ra Items hay bảng SalesHistory ban đầu thì nhìn item_no nếu nhìn là key thì có mối quan hệ 1 đối với nhiều. Hơn nữa, đối với tổ hợp thì quan hệ một chiều thì sẽ không tăng số dòng sau khi tổ hợp. Vừa đúng là nhân với 1 thì kết quả cũng không có thay đổi nào cả.
Trong trường hợp OUTER JOIN, vì thêm dòng 40 chỉ tồn tại trong MASTER nên không thể không tăng số dòng nhưng sẽ không xảy ra kết quả là số dòng của sản phẩm 10 và 20 tăng lên một cách không đúng. Để cải thiện điều đó thì chúng ta có truy vấn sau đây,
--Câu trả lời 2: Tiến hành kết hợp 1 đối nhiều trước khi tập hợp
SELECT I.item_no, SUM(SH.quantity) AS total_qty
FROM Items I LEFT OUTER JOIN SalesHistory SH
ON I.item_no = SH.item_no --Kết hợp 1 đối nhiều
GROUP BY I.item_no
Điểm mạnh khi dùng truy vấn này là code có thể viết một cách gọn gàng, không cần tạo view trung gian vì thế hướng đến tăng perfomance.
Và đối với trường hợp có dòng trùng trong phần item_no trong bảng Items thì nó sẽ trở thành nhiều đối nhiều nên phương pháp này sẽ không sử dụng được. Trong trường hợp này chúng ta phải tập hợp trước một trong 2 bảng và biến nó thành quan hệ một đối nhiều.
Kết hợp trong những trường hợp một đối một hay một đối nhiều thì số dòng sẽ không tăng lên một cách không bình thường
Đây là một kĩ thuật rất hiệu quả trong những vấn đề có sự kết hợp giữa tập hợp và kết hợp nên mọi người cần nhớ.
Đọc đến đây chắc hẳn cũng đã có người phát hiện ra đúng không. Thực ra đối với câu hỏi về tìm tổng dân số trước chúng ta cuxg có thể viết lại như trên. Vấn đề này có trong câu hỏi cuối bài nên mọi người hãy thử làm tại đó.
FULL OUTER JOIN
Nửa trên hầu hết chúng ta đã cận cảnh từ phía ứng dụng của OUTER JOIN từ một phía. Nửa sau thì chúng ta sẽ nhìn từ quan điểm hướng tập hợp về đặc tính của chính OUTER JOIN.
Trong SQL thông thường thì có 3 câu lệnh được định nghĩa về 3 chủng loại của OUTER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
Trong đó, giữa LEFT OUTER JOIN và RIGHT OUTER JOIN thì không có sự khác nhau lớn. Bảng master nếu muốn viết phép toán về pháo bên trái thì là LEFT OUTER JOIN, nếu viết về phía phải thì là RIGHT OUTER JOIN. Về 2 cái này thì chắc chắn mọi người cũng đã hiểu rất rõ. Tại đây sẽ nêu ra FULL OUTER JOIN mà chưa được biết về nhiều nhất trong 3 chủng loại này. Nếu nhìn từ quan điểm của phép tính tập hợp thì chúng ta sẽ nhìn thấy được những đặc trưng thú vị của OUTER JOIN.
FULL OUTER JOIN là gì thì so với việc giải thích bằng từ ngữ thì nhìn bằng mắt sẽ dễ hiểu hơn nên chúng ta nhanh chóng vào với bảng đơn giản như dưới đây.
Class_A| id | name |
|---|---|
| 1 | Tanaka |
| 2 | Suzuki |
| 3 | Ijuin |
| id | name |
|---|---|
| 1 | Tanaka |
| 2 | Suzuki |
| 4 | Saionji |
Trong những học sinh trực thuộc của 2 lớp như trên có tồn tại 2 người là Tanaka và Suzuki mà có cả ở 2 bảng. Tuy nhiên những tên như Ijuin hay Saionji thì chỉ tồn tại ở 1 bảng thôi. FULL OUTER JOIN chính là phương pháo có được kết quả mà không làm mất thông tin nào từ 2 bảng có nội dung dữ liệu không đồng nhất với nhau. Có nghĩa theo một cách nói khác là tổ hợp sử dụng master từ 2 phía.
--FULL OUTER JOIN là lưu thông tin một cách hoàn toàn
SELECT COALESCE(A.id, B.id) AS id
A.name AS A_name
B.name AS B_name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id
Kết quả
id A_name B_name --- ------- ------- 1 Tanaka Tanaka 2 Suzuki Suzuki 3 Ijuin 4 Saionji
Tại đây có hiện kết quả của cả 4 người tồn tại trong 2 bảng. COALESCE là hàm số thông thường trả về biểu thức có giá trị khác NULL đầu tiên trong số những biểu thức được truyền vào COALESCE. Trong trường hợp LEFT OUTER JOIN (hoặc là RIGHT OUTER JOIN) thì đó chỉ trả lại một phía trái hoặc phải của bảng master sử dụng nên không thể nào có cả 2 kết quả là Ijuin hay Saionji được. FULL trong FULL OUTER JOIN có nghĩa là lưu thông tin một cách hoàn toàn.
Tiếp theo để có được kết quả tương tự trong môi trường không thể sử dụng FULL OUTER JOIN thì chúng ta sẽ UNION kết quả của LEFT OUTER JOIN và RIGHT OUTER JOIN.
--Phương pháp thay thế trong môi trường không thể sử dụng FULL OUTER JOIN
SELECT A.id AS id, A.name, B.name
FROM Class_A A LEFT OUTER JOIN Class_B B
ON A.id = B.id
UNION
SELECT B.id AS id, A.name, B.name
FROM Class_A A RIGHT OUTER JOIN Class_B B
ON A.id = B.id;
Như vậy chúng ta có thể có được kết quả y hệt như vậy, nhưng đây cũng là đường vòng, rồi dùng UNION từ phía mặt perfomance thì cũng không được hoan nghênh.
Tuy nhiên nếu thay đổi điểm nhìn một chút thì chúng ta có thể nhìn tổ hợp bằng các phép toán tập hợp. Trong tổ hợp nội bộ thì ta có phép giao (INTERSECT) đảm nhiệm còn OUTER JOIN thì có phép hợp (UNION) đảm nhiệm. Nếu vẽ dưới dạng biểu đồ thì ta có thể vẽ được như dưới đây.
Về điểm không làm rơi thông tin nào thì đây là điểm giống nhau giữa UNION và OUTER JOIN( Và cũng giống như câu lệnh MERGE). Bậc tiếp theo thì chúng ta sẽ sử dụng đặc tính này của OUTER JOIN để thử phép tính tổ hợp trên thực tế.
Phép tính tổ hợp bằng OUTER JOIN
Trong SQL thì lý luận về tập hợp là cơ bản nhưng ngoại lệ thì cho đến dần đây thì trong SQL không còn mang những tính năng của phép toán tập hợp cơ bản này nữa. UNION là bản cũ có từ SQL-86 nhưng những thứ như INTERSECT hay EXCEPT thì từ SQL-92 mới được đưa vào và phép chia vẫn chưa được thông thường hóa thì việc này đã được nêu ra trong chương trước. Rồi tình trạng thực hành của những DBMS khác nhau cũng chưa đầy đủ nên vẫn chưa được thống nhất. Phép toán tập hợp sẽ làm phát sinh sort nên có khả năng sẽ kéo theo vấn đề về perfomance. Nên chúng ta có cần thiết phải biết đến những phương án thay thế của những phép toán tập hợp này.
Tiếp theo sau tổng và giao thì chúng ta sẽ nghĩ tiếp phương pháp tính hiệu. Nếu chú ý vào kết quả của FULL OUTER JOIN trên thì dòng ijuin có ở lớp A mà không có ở lớp B nên chúng ta có thể hiểu ở dòng B_name sẽ trả lại kết quả NULL. Ngược lại Saionji có ở lớp B mà không có ở lớp A thì trên dòng A_name sẽ trả lại kết quả NULL. Có nghĩa là trong kết quả của JOIN thì điều kiện NULL có được xác lập không thì đó chính là hiệu của tổ hợp đã được tính toán.
Yêu cầu hiệu của tổ hợp bằng OUTER JOIN: A-B
SELECT A.id AS id, A.name AS A_name
FROM Class_A A LEFT OUTER JOIN Class_B B
ON A.id = B.id
WHERE B.name IS NULL;
Kết quả
id A_name ---- -------- 3 IjuinHiệu tổ hợp bằng OUTER JOIN(A-B)
Yêu cầu hiệu của tổ hợp bằng OUTER JOIN: B-A
SELECT A.id AS id, A.name AS A_name
FROM Class_A A LEFT OUTER JOIN Class_B B
ON A.id = B.id
WHERE A.name IS NULL;
Kết quả
id B_name ---- -------- 4 SaionjiHiệu tổ hợp bằng OUTER JOIN(B-A)
Cái này tất nhiên không thể nói đây là cách dùng chính đạo với tư cách là cách dùng OUTER JOIN. OUTER JOIN không phải là thứ tồn tại để được dùng như thế này. Tuy nhiên trong trường hợp thực hiện không phải là phép trừ thì chúng ta có thể dùng NOT IN hoặc NOT EXISTS cũng là một lựa chọn. Thực ra đây có thể nói là động tác có khả năng nhanh nhất và là một trong những thủ thuật có thể sử dụng.
Yêu cầu tập hợp tổng loại trừ bằng FULL OUTER JOIN
Tiếp theo, chúng ta sẽ suy nghĩ cách lấy tập hợp tổng loại trừ của tập hợp A và B. Trong SQL thì không có phép tính chuyên dụng cho việc đó nên nếu sử dụng phép toán tập hợp thì chúng ta có phương pháp như (A EXCEPT B) UNION (B EXCEPT A) hoặc (A UNION B) EXCEPT (A INTERSECT B). Phương pháp nào thì cũng khá phức tạp và tốn nhiều tài nguyên.
Một lần nữa ta nhìn kĩ lại kết quả của FULL OUTER JOIN ... Mọi người đã nhìn ra chưa?
SELECT COALESCE(A.id, B.id) AS id,
COALESCE(A.name , B.name ) AS name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id
WHERE A.name IS NULL
OR B.name IS NULL;
Kết quả
id name ---- -------- 3 Ijuin 4 SaionjiTập hợp tổng loại trừ bằng FULL OUTER JOIN
Tại đây nếu chúng ta thay đổi điều kiện trong câu lệnh WHERE thì ta có thể biểu hiện những phép toán tập hợp khác nhau. Tại đây chúng ta có thể yêu cầu tổng, hiệu và giao. Vậy thì tại đây tích thì thương thì chúng ta làm thế nào? Thực ra cái này cũng có thể tính được bằng OUTER JOIN. Có nghĩa là với phép chia quan hệ đã giới thiệu tại "Sức mạnh của câu lệnh HAVING" thì chúng ta có thẻ viết được bằng OUTER JOIN. Nếu sử dụng bảng Items và bảng ShopItems dduwwocj nêu ra trong chương "Sức mạnh của câu lệnh HAVING" thì chúng ta có thể viết như dưới đây.
--Phép chia quan hệ bằng OUTER JOIN: Ứng dụng của tập hợp hiệu
SELECT DISTINCT shop
FROM ShopItems SI1
WHERE NOT EXISTS
(SELECT I.item
FROM Items I LEFT OUTER JOIN ShopItems SI2
ON I.item = SI2.item
AND SI1.shop = SI2.shop
WHERE SI2.item IS NULL) ;
Kết quả
shop ------ Sedai Tokyo
Ý nghĩa của truy vấn này là từ bảng Items nếu kết quả từ phép trừ sản phẩm của từng cửa hàng thì nếu là tập hợp rỗng thì có nghĩa là cửa hàng đó có tất cả những sản phẩm có trong bảng Items. Theo subquery của câu lệnh ON "SI1.shop = SI2.shop" có nghĩa là ta viết điều kiện từng cửa hàng. Phương pháp này sử dụng hiệu của tập hợp nên chúng ta cũng có thể một cách trực tiếp viết EXCEPT. Các bạn hãy thử viết thay thế trong bài tập luyện tập sau này.
Tóm tắt
Cuối cùng chúng ta đã chạm một chút vào câu văn của OUTER JOIN. SQL là ngôn ngữ có rất nhiều tiếng địa phương nhưng bên trong OUTER JOIN sử dụng (+) nếu là Oracle hay (*=) nếu là SQLServer nên là một công cụ rất mạnh. Nếu nghĩ về sự thông dụng trong code thì ta nên cố tránh những cách viết thế này mà nên sử dụng những cách viết thống nhất mang tính thông thường.
Mặt khác, OUTER có thể giản lược nên chúng ta cũng có thể sử dụng cách viết LEFT JOIN, RIGHT JOIN hay FULL JOIN (Vấn đề này nói chung trong SQL thông thường thì vẫn được chấp nhận). Nhưng để phân biệt với tổ hợp nội bộ thì chúng ta cũng nên viết vào. Mọi người trong lúc làm việc hãy chú ý một chút những điểm như vậy. (Về vấn đề từ toàn dân hay tiếng địa phương thì chúng ta có thể tham khảo một lần trong "Phương pháp lập trình SQL".
Như vậy trọng điểm của lần này là
-SQL không phải là ngôn ngữ để tạo phiếu nên cơ bản không hướng tới để điều chỉnh format.
-Trong trường hợp đòi hỏi cần thiết thì chúng ta có thể dùng hàm CASE hoặc OUTER JOIN
-Trong trường hợp tạo lồng dòng thì phải tạo bảng master rồi từ đó quyết định tổ hợp một lần
-Trong trường hợp chú ý đến số dòng thì suy nghĩ tập hợp là phép nhân. Vậy nên kể cả trong trường hợp tập hợp quan hệ 1 với nhiều thì cũng không làm tăng số dòng
-OUTER JOIN có thể được suy nghĩ như một phép tính tập hợp. Từ quan điểm đó thì chúng ta có thể biểu hiện nhiều loại phép tính tổng hợp.
OUTER JOIN là kĩ thuật được sử dụng như thường ngày nhưng điểm có thể sử dụng những kĩ thuật thân quen đó để phát triển sử dụng rộng hơn thì đó là một trong những điều thú vị của SQL.
Để muốn tìm hiểu kĩ hơn về OUTER JOIN thì có thể tham khảo những tài liệu dưới đây. 1.『標準SQLガイド 改訂第4版』 C.J.Date ・ Hugh Darwen 著、Quipu LLC 訳、アスキー、1998年12月
Tham khảo chương 11 về những câu lệnh thông thường của OUTER JOIN.
2.『プログラマのためのSQL 第2版』 Joe Celko 著、秋田 昌幸 訳、ピアソンエデュケーション、2001年4月
Về những phép tính tập hợp sử dụng OUTER JOIN là "26.2.1 Hiệu của tập hợp và OUTER JOIN", về phép chia quan hệ sử dụng hiệu tập hợp là "19.2.6 Phép chia dựa vào phép tính tập hợp", về những người chưa biết về bảng có những đề mục lặp lại ở đâu thì có thể tham khảo ở chương 25.
3.『SQLパズル 第2版』 Joe Celko 著、ミック 訳、翔泳社、2007年11月
Ở đây được sử dụng rất nhiều những vấn đề về OUTER JOIN, về đại diện thì có thể kể đến ứng dụng đổi dòng cột "Puzzle 14 Điện thoại và FAX" và "Puzzle 55 Cuộc đua ngựa và số lần nhận giải", những phép tính tập hợp sử dụng OUTER JOIN là "Puzzle 58 Tìm điểm khuyết version 2", về ứng dụng phép chia quan hệ là "Puzzle 21 Máy bay và phi công".
`
