Hướng dẫn SQL & XQuery cho IBM DB2, Phần 3: Phép nối và phép hợp SQL

Các truy vấn phức tạp bao gồm nhiều hơn một bảng quan hệ

Hướng dẫn này mô tả hai cách kết hợp dữ liệu từ các bảng khác nhau trong một cơ sở dữ liệu IBM® DB2® nối các bảng với phép nối trong hay phép nối ngoài và cách sử dụng các toán tử UNION, EXCEPT và INTERSECT để nhận được tập kết quả trung gian từ một biểu thức truy vấn và kết hợp nó với tập kết quả từ biểu thức truy vấn khác. Hướng dẫn này là Phần 3 của loạt bài hướng dẫn .

Jessica Cao, Chuyên viên phát triển các công cụ đào tạo, IBM

Jessica Cao là một sinh viên ngành Nghệ thuật - Khoa học và ngành Khoa học máy tính tại Trường đại học McMaster. Chị sẽ tốt nghiệp văn bằng kép hạng danh dự vào tháng Tư 2009. Jessica đang làm việc trong chương trình hỗ trợ và lập kế hoạch kênh kỹ năng quản lý thông tin DB2 của phòng thí nghiệm IBM Toronto, nhằm chuyên chú sâu hơn vào lập trình, biên tập và kỹ năng viết



Bruce Creighton, Chuyên viên lập kế hoạch các phần kỹ năng, IBM

Bruce Creighton là chuyên viên lập kế hoạch các phần kỹ năng trong khoa Phát triển và Lập kế hoạch đào tạo Quản lý thông tin. Với vai trò này, ông lập kế hoạch đầu tư về nội dung đào tạo và cân bằng đầu tư giữa các lĩnh vực mà IBM có thu lợi nhuận với các lĩnh vực mà tại đó yêu cầu về phát triển các kỹ năng là quan trọng, cần cung cấp đào tạo miễn phí



Pat Moffatt, Giám đốc Chương trình quản lý thông tin, Sáng kiến học đường, IBM

Pat Moffatt là giám đốc Chương trình quản lý thông tin thuộc Sáng kiến học đường IBM. Thông qua chương trình sáng kiến học đường này, bà muốn làm ra sẵn những nguồn tài nguyên thích hợp phục vụ giảng dạy về quản lý thông tin để giúp các giảng viên đại học tích hợp chủ đề phần mềm quản lý thông tin vào trong chương trình đào tạo của họ



20 05 2009

Trước khi bạn bắt đầu

Về loạt bài này

Loạt bài hướng dẫn này dạy các đề tài SQL từ cơ bản đến cao cấp và XQuery cơ bản đồng thời chỉ ra cách diễn đạt các câu hỏi kinh doanh thường gặp như là các truy vấn cơ sở dữ liệu khi sử dụng các truy vấn SQL hay các truy vấn XQuery. Những người phát triển và những người quản trị cơ sở dữ liệu có thể sử dụng hướng dẫn này để nâng cao các kỹ năng truy vấn cơ sở dữ liệu của họ. Các thành viên sáng kiến học đường có thể sử dụng loạt bài hướng dẫn này làm một phần của chương trình giảng dạy cơ sở dữ liệu của họ.

Tất cả các ví dụ trong tài liệu này được dựa trên Aroma, một cơ sở dữ liệu mẫu có chứa các dữ liệu doanh thu về các sản phẩm cà phê và chè đã bán trong các cửa hàng khắp nước Mỹ. Mỗi ví dụ gồm có ba phần:

  • Một câu hỏi kinh doanh, được diễn đạt bằng ngôn ngữ hàng ngày
  • Một hoặc nhiều ví dụ truy vấn, được biểu diễn bằng SQL hay Xquery
  • Một bảng các kết quả được cơ sở dữ liệu trả về

Cách trình bày hướng dẫn này cho phép các học viên tìm hiểu về ngôn ngữ SQL và XQuery. Cũng như với bất kỳ việc học tập nào, việc bổ sung thêm các bài tập thực hành là rất quan trọng. Dữ liệu và các định nghĩa bảng tạo điều kiện thuận lợi cho các bài tập thực hành này.

Đối với các sinh viên khi sử dụng tài liệu này như phần của khoá học, hãy nhận từ người hướng dẫn của mình các chỉ dẫn kết nối tới cơ sở dữ liệu Aroma và tìm hiểu về bất kỳ các sự khác nhau nào giữa hướng dẫn và cài đặt tại chỗ của bạn.

Hướng dẫn này được viết cho Express-C 9 DB2 trên nền UNIX®, Linux® và Windows® (trước đây gọi là Viper).

Về hướng dẫn này

Hướng dẫn này mô tả hai cách để kết hợp dữ liệu từ các bảng khác nhau trong một cơ sở dữ liệu IBM DB2:

  • Bằng cách kết nối các bảng
  • Bằng cách sử dụng các toán tử UNION, EXCEPT, và INTERSECT

Phần đầu tiên của hướng dẫn này trình bày các ví dụ về các kết nối bên trong và bên ngoài.

Phần thứ hai phần minh họa cách kết hợp dữ liệu từ các bảng khác nhau khi sử dụng các toán tử UNION, EXCEPT, và INTERSECT, để nhận được tập kết quả trung gian từ một biểu thức truy vấn và kết hợp nó với tập kết quả từ biểu thức truy vấn khác.

Nối tới một cơ sở dữ liệu

Bạn cần nối tới một cơ sở dữ liệu trước khi có thể sử dụng các câu lệnh SQL để truy vấn hay xử lý dữ liệu. Lệnh CONNECT kết hợp một kết nối cơ sở dữ liệu với một tên người sử dụng.

Thông qua người hướng dẫn tìm ra tên cơ sở dữ liệu bạn cần nối tới. Đối với loạt bài này, tên cơ sở dữ liệu là aromadb.

Để nối tới cơ sở dữ liệu aromadb, gõ lệnh sau đây vào trong bộ xử lý dòng lệnh DB2:

		CONNECT TO aromadb USER userid USING password

Thay thế "userid" và "password" bằng số ID và mật khẩu của người sử dụng mà bạn đã nhận được từ thầy hướng dẫn. Nếu máy không hỏi userid và password thì chỉ cần gõ vào lệnh sau đây:

		CONNECT TO aromadb

Thông điệp sau báo cho bạn biết rằng đã tạo kết nối thành công:

		Database Connection Information
		Database server      = DB2/NT 9.0.0
		SQL authorization ID = USERID
		Local database alias = AROMADB

Khi đã kết nối được, bạn có thể bắt đầu sử dụng cơ sở dữ liệu.


Các tên và các lược đồ bảng

Một lược đồ là gì?

Một lược đồ được sử dụng để tạo nhóm. Nói đơn giản nhất, một lược đồ giống như là một khu vực trong một thư viện công cộng: các cuốn sách được nhóm lại thành các khu vực khác nhau. Để tìm các sách lịch sử bạn đi đến khu vực lịch sử. Tương tự, trong một cơ sở dữ liệu (giống như thư viện), các bảng được nhóm vào trong các lược đồ khác nhau. Khi một người sử dụng đăng nhập vào cơ sở dữ liệu, nhận dạng của người sử dụng trở thành một lược đồ mặc định cho tất cả các truy vấn.

Ví dụ, nếu một người sử dụng có tên DBUSER thực hiện một truy vấn như

		SELECT ... FROM sales

hệ thống hiểu điều đó có nghĩa là

		SELECT ... FROM dbuser.sales

Nếu không có lược đồ dbuser trong cơ sở dữ liệu hệ thống trả về một lỗi.

Có hai phương pháp để tránh lỗi này. Một là sử dụng các tên bảng đầy đủ cho mỗi bảng; hai là đặt một bộ nhận biết lược đồ mặc định khác.

Sử dụng các tên bảng đầy đủ

Bạn có thể nói rõ tên bảng hoàn toàn đầy đủ trong mệnh đề FROM của mỗi truy vấn. Để làm điều này, bạn thêm tên của lược đồ và một dấu chấm vào trước tên từng bảng như sau

		SELECT ... FROM aroma.sales ...

Có hai lợi thế rõ ràng khi sử dụng các tên bảng thích hợp:

  1. Bất kỳ người sử dụng nào có thể xem xét truy vấn và biết chính xác truy vấn được viết đối với lược đồ nào.
  2. Bạn có thể gộp các bảng từ các lược đồ khác vào trong cùng truy vấn, với điều kiện rằng người sử dụng có quyền truy nhập dữ liệu từ các lược đồ đó, như sau:
    		SELECT ... FROM aroma.sales, dbuser.customer ...

Tuy nhiên, nếu bạn sẽ thực hiện nhiều truy vấn từ chỉ một lược đồ, bạn có thể không muốn phải gõ vào tất cả các tên bảng đầy đủ. Bạn có thể tiết kiệm gõ phím bằng cách cài đặt một lược đồ mặc định mới.

Thiết lập một lược đồ mặc định mới

Việc thiết lập một lược đồ mới chỉ cần một câu lệnh đơn giản:

				SET SCHEMA <schema_name>;

Từ điểm này trở đi, hệ thống sử dụng tên lược đồ mới như là lược đồ mặc định, vì vậy chỉ cần sử dụng các tên bảng không kèm tên lược đồ (unqualified). Bạn vẫn còn có thể bao gồm các bảng từ các lược đồ khác bằng cách sử dụng các tên bảng đầy đủ, như:

		SET SCHEMA aroma;
		SELECT ... FROM sales, dbuser.customer ...

Tuy nhiên, người sử dụng phải được cấp phép thích hợp để làm việc trong lược đồ mới, hoặc các truy vấn và các hành động khác sẽ thất bại.

Việc thiết lập lược đồ chỉ duy trì hiệu lực trong phiên hiện tại của bạn. Nếu bạn ngắt ra khỏi cơ sở dữ liệu, bạn phải chạy lại lệnh SET cho loạt các truy vấn tiếp theo của mình.

Các lưu ý về cách dùng

Các truy vấn trong loạt bài này sẽ sử dụng các tên bảng đầy đủ.

Các bí danh của bảng

Bạn cũng có thể tiết kiệm gõ phím và làm cho các truy vấn dễ đọc hơn bằng cách tận dụng các bí danh bảng. Các bí danh bảng nói chung là một tên được rút ngắn cho một bảng, được gán trong mệnh đề FROM và được sử dụng trong suốt phần còn lại của truy vấn. Chúng thường được sử dụng nhất khi một truy vấn kết nối nhiều bảng.

Ví dụ, một truy vấn nối bảng đơn giản có thể là:

		SELECT date, dollars
		FROM aroma.period, aroma.sales
		WHERE   aroma.period.perkey = aroma.sales.perkey
		    AND aroma.period.month = 'JAN'
		    AND aroma.period.year = 2006;

Bạn có thể viết lại truy vấn này khi sử dụng các bí danh bảng. Trong trường hợp này, chúng ta sẽ sử dụng bí danh "a" cho bảng Period và "b" cho các bảng Sales:

		SELECT date, dollars
		FROM aroma.period a, aroma.sales b
		WHERE   a.perkey = b.perkey
		    AND a.month = 'JAN'
		    AND a.year = 2006;

Mỗi tên bảng đầy đủ trong mệnh đề FROM được nối tiếp theo sau bằng một khoảng trống và tên bí danh. Bạn có thể gán các bí danh cho một vài, cho tất cả các bảng hay không cho bảng nào được liệt kê trong mệnh đề FROM.

Tuy nhiên, khi bạn gán một bí danh bảng trong mệnh đề FROM bạn phải sử dụng tên bí danh mỗi khi bạn tham chiếu đến bảng đó trong truy vấn. Bạn sẽ gặp các thông báo lỗi nếu bạn cố sử dụng tên bảng đầy đủ ở nơi khác trong truy vấn.

Khi gán một tên bí danh, bạn cũng phải cẩn thận không sử dụng tên của một bảng hiện có trong lược đồ hoặc các kết quả của bạn có thể sai.

Trong ví dụ ở trên, giả thiết rằng người sử dụng dbuser đã đăng nhập và không thay đổi lược đồ mặc định. Nếu đã có một bảng được đặt tên "a" trong lược đồ, hệ thống sẽ tìm kiếm cột dbuser.a.perkey thay vì thay thế nó bằng giá trị aroma.period.perkey.

Các lưu ý về cách dùng

Hầu hết các truy vấn trong hướng dẫn này sẽ lợi dụng ưu điểm của các bí danh bảng.


Sử dụng phép nối đơn giản

Câu hỏi

Tổng doanh thu hàng ngày của các sản phẩm Lễ phục sinh đã bán ra vào kỳ nghỉ cuối tuần theo kiểu khuyến mại 900 trong năm 2005 là bao nhiêu và các cửa hàng nào đã ghi nhận doanh thu đó?

Ví dụ truy vấn

		SELECT prod_name, store_name, day, dollars
		FROM aroma.promotion a, aroma.product b,
		     aroma.period c, aroma.store d, aroma.sales e
		WHERE    a.promokey = e.promokey 
		    AND   b.prodkey = e.prodkey 
		    AND   b.classkey = e.classkey 
		    AND   c.perkey = e.perkey 
		    AND   d.storekey = e.storekey
		    AND   prod_name LIKE 'Easter%'
		    AND   day IN ('SA', 'SU')
		    AND   promo_type = 900
		    AND   year = 2005;

Kết quả

Prod_NameStore_NameDayDollars
Easter Sampler BasketOlympic Coffee CompanySA150.00

Về truy vấn

Câu hỏi kinh doanh này đòi hỏi nối năm bảng trong lược đồ bán lẻ Aroma: bảng sự kiện Sales và các bảng Product, Period, Store, và Promotion của nó. Xem lại Phần 1 trong loạt bài hướng dẫn này để thấy sự bố trí bảng cho cơ sở dữ liệu Aroma.

Để nối các bảng trong một truy vấn, bạn phải cung cấp cho máy chủ cơ sở dữ liệu các chỉ dẫn rõ ràng về cách thực hiện kết nối. Cách nối được chỉ rõ trong mệnh đề WHERE với năm điều kiện đơn giản để nối bảng Sales qua năm cột khóa chính của nó. Bảng Product có một khóa chính hai phần, vì vậy nó được nối tới bảng Sales trên hai cột : ProdkeyClasskey.

Các lưu ý về cách dùng

Bất kỳ hai bảng nào cũng có thể được nối qua các cột với các kiểu dữ liệu có thể so sánh được; phép nối không phụ thuộc vào các mối quan hệ của khóa chính với khóa ngoại được dùng trong ví dụ này.


Sử dụng mệnh đề ORDER BY

Câu hỏi

Các số doanh thu của Assam Gold Blend và Earl Grey tại các cửa hàng cà phê hoà tan trong tháng mười một năm 2005 là bao nhiêu? Sắp xếp thứ tự các số này cho mỗi sản phẩm từ cao nhất tới thấp nhất.

Ví dụ truy vấn

		SELECT prod_name, store_name, dollars
		FROM aroma.store a, aroma.sales b, aroma.product c, aroma.period d
		WHERE   a.storekey = b.storekey
		    AND c.prodkey = b.prodkey
		    AND c.classkey = b.classkey
		    AND d.perkey = b.perkey
		    AND (prod_name like 'Assam Gold%' OR prod_name LIKE 'Earl%')
		    AND store_name LIKE 'Instant%'
		    AND month = 'NOV'
		    AND year = 2005
		ORDER BY prod_name, dollars DESC;

Kết quả

Prod_NameStore_NameDollars
Assam Gold BlendInstant Coffee96.00
Assam Gold BlendInstant Coffee78.00
Assam Gold BlendInstant Coffee66.00
Assam Gold BlendInstant Coffee58.50
Assam Gold BlendInstant Coffee58.50
Assam Gold BlendInstant Coffee39.00
Assam Gold BlendInstant Coffee39.00
Assam Gold BlendInstant Coffee32.50
Earl GreyInstant Coffee48.00
Earl GreyInstant Coffee45.50
Earl GreyInstant Coffee42.00
Earl GreyInstant Coffee32.00
Earl GreyInstant Coffee24.00
Earl GreyInstant Coffee20.00

Sắp xếp thứ tự bảng kết quả: mệnh đề ORDER BY

Bạn có thể sử dụng mệnh đề ORDER BY để sắp xếp bảng kết quả của một truy vấn theo các giá trị trong một hoặc nhiều cột đã chỉ ra. Thứ tự sắp xếp mặc định là tăng dần (ASC); từ khóa DESC thay đổi thứ tự sắp xếp thành giảm dần cho cột đã chỉ ra như sau:

		ORDER BY prod_name, 3 DESC

Cú pháp của mệnh đề ORDER BY

		SELECT column name(s)
		FROM table name(s)
		[WHERE search_condition]
		[ORDER BY order_list];
order_listMột danh sách các cột mà dữ liệu được sắp xếp thứ tự theo các cột này. Các cột trong order_list không nhất thiết cần phải xuất hiện trong select_list nhưng phải tồn tại trong các bảng được tham chiếu trong mệnh đề FROM.

Về truy vấn

Ví dụ truy vấn lấy ra con số doanh thu Assam Gold Blend và Earl Grey tại cửa hàng cà phê hoà tan trong tháng Mười Một 2005. Truy vấn sắp xếp kết quả theo sản phẩm và tổng doanh thu hằng ngày.

Các lưu ý về cách dùngs

Mệnh đề ORDER BY phải tiếp theo sau các mệnh đề khác trong lệnh SELECT và bao gồm một danh sách các cột sẽ được sắp xếp theo thứ tự. Một cột có thể được tham chiếu theo tên, bí danh cột hay vị trí (số thứ tự) của nó trong danh sách select. Ví dụ, mệnh đề ORDER BY trên trang hiện tại có thể được viết như như sau:

		ORDER BY prod_name, 3 DESC

Bằng cách chỉ rõ các cột trong order_list mà không có trong danh sách tên cột, bạn có thể sắp xếp thứ tự dữ liệu theo các cột không được hiểu thị trong bảng kết quả.


Nối hai bảng

Sử dụng mẫu bảng sau cho các ví dụ truy vấn nối bảng ban đầu:

State TableRegion Table
CityStateCityArea
JacksonvilleFLJacksonvilleSouth
MiamiFLMiamiSouth
NashvilleTNNew OrleansSouth

Ví dụ truy vấn

		SELECT *
		FROM aroma.state, aroma.region;

Tích Đề các (thuộc tính nối bảng không được chỉ rõ)

CityStateCityArea
JacksonvilleFLJacksonvilleSouth
JacksonvilleFLMiamiSouth
JacksonvilleFLNew OrleansSouth
MiamiFLJacksonvilleSouth
MiamiFLMiamiSouth
MiamiFLNew OrleansSouth
NashvilleTNJacksonvilleSouth
NashvilleTNMiamiSouth
NashvilleTNNew OrleansSouth

Chú ý: các kết quả của bạn có thể được trình bày với thứ tự khác. Không có mệnh đề "ORDER BY", hệ thống trả về các hàng theo trình tự bất kỳ như chúng được tìm thấy.

Ví dụ truy vấn

		SELECT *
		FROM aroma.state, aroma.region
		WHERE state.city = region.city;

Tập con của tích Đề các (thuộc tính nối bảng được chỉ rõ)

State:CityState:StateRegion:CityRegion:Area
JacksonvilleFLJacksonvilleSouth
MiamiFLMiamiSouth

Phép nối trong

Đa số các truy vấn kết nối thông tin từ các bảng khác nhau. Bất kỳ hai bảng nào cũng có thể được nối qua các cột với các kiểu dữ liệu có thể so sánh được; các kết nối không phụ thuộc vào mối quan hệ của khóa chính với khóa ngoài.

Tích Đề các

Khi hai hoặc nhiều bảng được tham chiếu trong mệnh đề FROM của một truy vấn, máy chủ cơ sở dữ liệu nối các bảng. Nếu mệnh đề FROM cũng như mệnh đề WHERE không chỉ rõ một thuộc tính kết nối nào, máy chủ sẽ tính toán một tích Đề các bao gồm m* n hàng, ở đây m là số lượng các hàng trong bảng đầu tiên và n là số các hàng trong bảng thứ hai. Tích này là tập hợp của tất cả các cách kết hợp có thể được, tạo thành bằng cách nối một hàng từ bảng đầu tiên với một hàng từ bảng thứ hai.

Tập con của tích Đề các

Nếu các bảng được nối rõ ràng qua các cột với các kiểu dữ liệu có thể so sánh được, máy chủ sẽ tính toán một tập con của tích Đề các. Tập con này chỉ chứa đựng những hàng nào mà ở đó các giá trị trong các cột dùng để kết nối khớp với nhau. Trong thời gian truy vấn, các tập con có chức năng như là một bảng dẫn xuất và có thể được kết nối với các bảng khác hay các kết quả của các biểu thức truy vấn khác.

Về truy vấn

Cả hai bảng StateRegion chứa các cột City được chỉ rõ như là các cột kết nối trong mệnh đề WHERE. Vậy thì, chỉ có các hàng nào của tích Đề các mà có các khóa City khớp nhau, sẽ được hiển thị trong kết quả. Trong ví dụ truy vấn, bảng kết quả chỉ chứa có hai hàng, trong khi tích Đề các đầy đủ của cả hai bảng này có chín hàng.


Một cách khác để kết nối các bảng

Câu hỏi

Hiển thị một danh sách của tất cả tên sản phẩm bắt đầu bằng chữ cái viết hoa "A" và các kiểu lớp của chúng. Sắp xếp danh sách sản phẩm theo thứ tự abc.

Ví dụ truy vấn 1

		SELECT prod_name, class_type
		FROM    aroma.product t, aroma.class c
		WHERE   t.classkey = c.classkey
		    AND prod_name LIKE 'A%'
		ORDER BY prod_name;

Ví dụ truy vấn 2

		SELECT prod_name, class_type
		FROM     aroma.product t
		    JOIN aroma.class c ON t.classkey = c.classkey
		WHERE prod_name LIKE 'A%'
		ORDER BY prod_name;

Hai truy vấn; cùng kết quả

PROD_NAMECLASS_TYPE
Aroma RomaBulk_beans
Aroma RomaPkg_coffee
...

Phép nối trong mệnh đề FROM

Bạn có thể kết nối rõ ràng các bảng trong mệnh đề FROM khi sử dụng cú pháp ON như được chỉ ra trong ví dụ ở trên.

Về truy vấn

Truy vấn này nối các bảng ProductClass qua các cột với các tên giống nhau. Các giá trị kết quả trong mệnh đề ON bởi vậy giống với các giá trị hiện ra trong truy vấn có ràng buộc kết nối trong mệnh đề WHERE.

Các lưu ý về cách dùng

Không có sự khác nhau về hiệu năng nào giữa các truy vấn ràng buộc phép nối bảng trong mệnh đề FROM, sử dụng cú pháp ON và các truy vấn với ràng buộc trong mệnh đề WHERE. Quyết định về việc sử dụng sự ràng buộc nào là của người tạo ra truy vấn.

Một số người thích cú pháp ràng buộc của mệnh đề FROM do nó tách biệt rõ ràng các sự ràng buộc được dùng để nối các bảng khỏi các ràng buộc được dùng để giới hạn các tập kết quả. Những người khác thích liệt kê tất cả các sự ràng buộc trong mệnh đề WHERE.

Tuy nhiên, như các ví dụ đã cho thấy, hãy thực hiện nối các bảng qua mối quan hệ khóa chính/ khóa ngoài. Nói chung đây là cách hiệu quả nhất để nối các bảng. Để biết thêm chi tiết có thể xem trong SQL Reference Guide.


Tự kết nối

Các bảng đang được nối trong một truy vấn không nhất thiết phải khác biệt nhau; bạn có thể nối một bảng bất kỳ tới chính nó miễn là bạn đưa ra cho mỗi tham chiếu bảng một tên khác nhau. Tự kết nối có ích để tìm ra các mối quan hệ giữa các cột dữ liệu khác nhau trong cùng bảng.

Câu hỏi

Các sản phẩm nào trong bảng Product có cùng tên nhưng khác kiểu đóng gói ?

Ví dụ truy vấn

		SELECT a.prod_name AS products, a.pkg_type
		FROM aroma.product a, aroma.product b
		WHERE   a.prod_name = b.prod_name
		    AND a.pkg_type <> b.pkg_type
		ORDER BY products, a.pkg_type;

Kết quả

ProductPkg_Type
Aroma RomaNo pkg
Aroma RomaOne-pound bag
Assam Gold BlendNo pkg
Assam Gold BlendQtr-pound bag
Assam Grade ANo pkg
Assam Grade AQtr-pound bag
Breakfast BlendNo pkg
Breakfast BlendQtr-pound bag
Cafe Au LaitNo pkg
Cafe Au LaitOne-pound bag
ColombianoNo pkg
ColombianoOne-pound bag
Darjeeling Number 1No pkg
Darjeeling Number 1Qtr-pound bag
Darjeeling SpecialNo pkg
Darjeeling SpecialQtr-pound bag
Demitasse MsNo pkg
Demitasse MsOne-pound bag
Earl GreyNo pkg
Earl GreyQtr-pound bag
English BreakfastNo pkg
English BreakfastQtr-pound bag
Expresso XONo pkg
Expresso XOOne-pound bag
Gold TipsNo pkg
Gold TipsQtr-pound bag
Irish BreakfastNo pkg
Irish BreakfastQtr-pound bag
...

Về truy vấn

Truy vấn này nối bảng Product với chính nó trên cột Prod_Name sử dụng các bí danh a và b để phân biệt tham chiếu bảng:

		FROM aroma.product a, aroma.product b

Tự kết nối so sánh bảng Producta với bảng Productb để tìm các hàng ở đó có các tên sản phẩm khớp nhau nhưng có kiểu đóng gói khác nhau:

			WHERE a.prod_name = b.prod_name
		    AND a.pkg_type <> b.pkg_type

Tập kết quả gồm có một danh sách của mỗi cặp sản phẩm có tên giống nhau và các kiểu đóng gói riêng của chúng.


Phép nối ngoài hai bảng

State TableRegion Table
CityStateCityArea
JacksonvilleFLJacksonvilleSouth
MiamiFLMiamiSouth
NashvilleTNNew OrleansSouth

Hai bảng trên được dùng để giải thích phép nối ngoài bên trái, phép nối ngoài bên phải, và phép nối ngoài đầy đủ. Để đơn giản, lược đồ Aroma không được đưa vào trong các ví dụ này.

Ví dụ truy vấn (phép nối ngoài bên trái)

		SELECT *
		FROM state LEFT OUTER JOIN region
		    ON state.city = region.city;

Kết quả

State:CityState:StateRegion:CityRegion:Area
JacksonvilleFLJacksonvilleSouth
MiamiFLMiamiSouth
NashvilleTN[null][null]

Ví dụ truy vấn (phép nối ngoài bên phải)

		SELECT *
		FROM state RIGHT OUTER JOIN region
		    ON state.city = region.city;

Kết quả

State:CityState:StateRegion:CityRegion:Area
JacksonvilleFLJacksonvilleSouth
MiamiFLMiamiSouth
[null][null]New OrleansSouth

Ví dụ truy vấn (phép nối ngoài đầy đủ)

		SELECT *
		FROM state FULL OUTER JOIN region
		    ON state.city = region.city;

Kết quả

State:CityState:StateRegion:CityRegion:Area
JacksonvilleFLJacksonvilleSouth
MiamiFLMiamiSouth
NashvilleTN[null][null]
[null][null]New OrleansSouth

mũi tên Chú ý quan trọng: Các ví dụ này sử dụng các bảng được giới thiệu ở phần bắt đầu của hướng dẫn này.

Phép nối ngoài

Trong hầu hết các trường hợp, các bảng được nối theo các điều kiện tìm kiếm để chỉ tìm các hàng có các giá trị khớp; kiểu nối này được gọi là inner join. Tuy nhiên, trong một số trường hợp, việc phân tích hỗ trợ quyết định yêu cầu các outer joins, để lấy ra cả các hàng khớp và các hàng không khớp, ví dụ các hàng có quan hệ lớn hơn hoặc nhỏ hơn.

Một phép nối ngoài trả về tất cả các hàng được trả về bằng một kết nối bên trong cộng với tất cả các hàng từ một bảng không khớp với bất kỳ hàng nào từ bảng khác. Một phép nối ngoài có thể là kết nối bên trái, bên phải, hay đầy đủ, tuỳ thuộc vào các hàng từ các bảng nào sẽ được giữ lại, bảng bên trái, bên phải hay cả hai bảng. Bảng đầu tiên được liệt kê trong mệnh đề FROM được coi như bảng bên trái và bảng thứ hai được coi là bảng bên phải. Với tất cả ba kiểu nối ngoài, giá trị NULL được dùng để đại diện cho các cột rỗng trong các hàng mà không khớp.

Cú pháp

Như đã thấy trong các ví dụ trước, một phép nối ngoài giữa hai bảng có thể được chỉ rõ trong mệnh đề FROM với các từ khóa OUTER JOIN tiếp theo sau là mệnh đề phụ ON:

			FROM table_1 LEFT|RIGHT|FULL OUTER JOIN table_2
		    ON table_1.column = table_2.column

Để biết thêm chi tiết về các cách khác nhau để chỉ rõ các thuộc tính kết nối ngoài trong mệnh đề FROM, xem trong SQL Reference Guide.

Về các truy vấn

  • Kết quả của phép nối ngoài bên trái chứa mọi hàng từ bảng State và tất cả các hàng khớp trong bảng Region Các hàng chỉ được tìm thấy trong bảng Region không được hiển thị.
  • Kết quả của phép nối ngoài bên phải chứa mọi hàng từ bảng Region và tất cả các hàng khớp từ bảng State Các hàng chỉ được tìm thấy trong bảng State tkhông được hiển thị.
  • Kết quả của phép nối ngoài đầy đủ chứa các hàng mà chúng là duy nhất cho mỗi bảng, cũng như các hàng là chung cho cả hai bảng.

Sử dụng các hàm tập hợp SUM, AVG, MAX, MIN, COUNT

Câu hỏi

Các tổng doanh thu của Lotta Latte ở Los Angeles trong năm 2005 là bao nhiêu? Các số doanh thu trung bình, cực đại và cực tiểu hàng ngày trong năm đó là bao nhiêu, và các giá trị gộp này được tính dựa trên bao nhiêu các tổng doanh số hằng ngày?

Ví dụ truy vấn

	SELECT SUM(dollars) as Dol_Sales, AVG(dollars) as Avg_Sales, 
	          MAX(dollars) as Max_Sales, MIN(dollars) as Min_Sales, COUNT(*) as Qty
		FROM aroma.store a, aroma.sales b, aroma.product c, aroma.period d
		WHERE   a.storekey = b.storekey
		    AND c.prodkey = b.prodkey
		    AND c.classkey = b.classkey
		    AND d.perkey = b.perkey
		    AND prod_name LIKE 'Lotta Latte%'
		    AND year = 2005
		    AND city LIKE 'Los Ang%';

Kết quả

Dol_SalesAvg_SalesMax_SalesMin_SalesQty
13706.50171.33125000376.0039.0080

Sử dụng các hàm tập hợp

Các hàm tập hợp hoạt động với các nhóm các giá trị. Ví dụ, SUM(dollars) tính toán tổng số đôla được trả về trong một bảng kết quả, và AVG(dollars) trả về giá trị trung bình. Các hàm tập hợp SQL được liệt kê trong bảng sau có thể xuất hiện một hoặc nhiều lần trong danh sách chọn.

Hàm số Mô tả
SUM(expression)Tính toán tổng của tất cả các giá trị trong biểu thức.
SUM(DISTINCT expression)Tính toán tổng của các giá trị khác nhau trong biểu thức.
AVG(expression)Tính toán trung bình của tất cả các giá trị trong biểu thức.
AVG(DISTINCT expression)Tính toán trung bình của tất cả các giá trị khác nhau trong biểu thức.
MAX(expression)Xác định giá trị cực đại trong biểu thức.
MIN(expression)Xác định giá trị cực tiểu trong biểu thức.
COUNT(*)Đếm số các hàng trả về.
COUNT(expression)Đếm số các giá trị khác không trong biểu thức.
COUNT(DISTINCTexpression)Đếm số các giá trị khác không và khác nhau trong biểu thức.

Bạn có thể thay thế biểu thức bằng bất kỳ tên cột hay biểu thức số nào. Mỗi hàm số, trừ COUNT(*), bỏ qua các giá trị NULL khi tính toán để trả về các giá trị gộp .

Về truy vấn

Ví dụ truy vấn lấy ra các số doanh thu của Lotta Latte ở Los Angeles trong năm 2005. Tập hợp kết quả cũng bao gồm doanh thu trung bình, cực đại, và cực tiểu trong năm, và số các tổng hàng ngày mà các tính toán này dựa vào.

Các lưu ý về cách dùng

Nếu tập kết quả sẽ chứa các giá trị riêng lẻ cũng như các giá trị gộp lại, truy vấn phải chứa một mệnh đề GROUP BY. Xem phần kế tiếp về mệnh đề GROUP BY.


Sử dụng mệnh đề GROUP BY để nhóm các hàng

Câu hỏi

Tổng doanh thu hàng năm của cà phê cốc trong năm 2004 ở mỗi hạt là bao nhiêu? Doanh thu trung bình, cực đại, và cực tiểu trong giai đoạn này là bao nhiêu? Liệt kê các kết quả theo hạt.

Ví dụ truy vấn

			SELECT district AS district_city, SUM(dollars) AS dol_sales,
		    AVG(dollars) AS avg_sales, MAX(dollars) AS max_sales,
		    MIN(dollars) AS min_sales
		FROM aroma.store a, aroma.sales b, aroma.product c,
		     aroma.period d, aroma.market e
		WHERE   a.storekey = b.storekey
		    AND c.prodkey = b.prodkey
		    AND c.classkey = b.classkey
		    AND d.perkey = b.perkey
		    AND e.mktkey = a.mktkey
		    AND prod_name LIKE '%Mug%'
		    AND year = 2004
		GROUP BY district
		ORDER BY dol_sales DESC;

Kết quả

District_CityDol_SalesAvg_SalesMax_SalesMin_Sales
Atlanta1378.3035.3410256498.554.00
Los Angeles711.6030.9391304398.559.95
San Francisco410.4525.6531250054.755.00

Nhóm các hàng: mệnh đề GROUP BY

Các hàm tập hợp hoạt động trên tất cả các hàng của một bảng kết quả hoặc trên các nhóm hàng được định nghĩa bởi một mệnh đề GROUP BY. Ví dụ, bạn có thể nhóm lại doanh thu theo từng thị trường và tính toán các giá trị tổng, cực đại và cực tiểu tương ứng.

Syntax of the GROUP BY clause

		SELECT column name(s)
		FROM table name(s)
		[WHERE search_condition]
		[GROUP BY group_list]
		[ORDER BY order_list];
group_listlà một danh sách các tên cột hoặc trong số (các) tên cột hoặc thuộc các bảng được liệt kê trong mệnh đề FROM. Tất cả các cột không được gộp lại trong số (các) tên cột phải xuất hiện ở group_list.

Về truy vấn

Ví dụ truy vấn lấy ra tổng doanh thu hàng năm của mặt hàng cà phê cốc trong năm 2004 (chúng chỉ được bán ra trong ba hạt), sắp xếp thứ tự các số từ cao nhất tới thấp nhất. Nói khái quát, máy chủ xử lý truy vấn này như sau:

  1. Lấy ra tất cả các hàng dữ liệu từ các bảng được chỉ rõ trong mệnh đề FROM, nối các hàng từ các bảng riêng biệt và tạo ra một bảng kết quả trung gian.
  2. Giữ lại tất cả các hàng từ bảng kết quả trung gian thỏa mãn điều kiện tìm kiếm được chỉ rõ trong mệnh đề WHERE .
  3. Chia bảng kết quả thành các nhóm được chỉ rõ trong mệnh đề GROUP BY.
  4. Xử lý tất cả các hàm tập hợp trên các nhóm được chỉ rõ cho toàn bộ bảng kết quả.
  5. Sắp xếp thứ tự các kết quả theo mệnh đề ORDER BY .
  6. Chỉ trả về các cột nào được chỉ rõ trong danh sách lựa chọn.

Các lưu ý về cách dùng

Mệnh đề GROUP BY tham chiếu đến các mục trong danh sách lựa chọn bằng các tên cột hiện tại của chúng. Bạn không thể sử dụng một bí danh cột hay số nguyên vị trí trong mệnh đề GROUP BY .


Sử dụng mệnh đề GROUP BY để tạo ra nhiều nhóm

Câu hỏi

Tổng doanh thu trong mỗi thành phố trong năm 2004 và 2005 là bao nhiêu? Liệt kê các tên thành phố theo năm, trong từng vùng và hạt của chúng .

Ví dụ truy vấn

		SELECT year, region, district, city, SUM(dollars) AS sales
		FROM aroma.store a, aroma.sales b, aroma.product c,
		     aroma.period d, aroma.market e
		WHERE   a.storekey = b.storekey
		    AND c.prodkey = b.prodkey
		    AND c.classkey = b.classkey
		    AND d.perkey = b.perkey
		    AND e.mktkey = a.mktkey
		    AND year IN (2004, 2005)
		GROUP BY year, region, district, city
		ORDER BY year, region, district, city;

Kết quả

YearRegionDistrictCitySales
2004CentralChicagoChicago133462.75
2004CentralChicagoDetroit135023.50
2004CentralMinneapolisMilwaukee172321.50
2004NorthBostonBoston184647.50
2004NorthBostonHartford69196.25
2004NorthNew YorkNew York181735.00
2004NorthNew YorkPhiladelphia172395.75
2004SouthAtlantaAtlanta230346.45
2004SouthAtlantaMiami220519.75
2004SouthNew OrleansHouston183853.75
2004SouthNew OrleansNew Orleans193052.25
2004WestLos AngelesLos Angeles219397.20
2004WestLos AngelesPhoenix192605.25
2004WestSan FranciscoCupertino180088.75
2004WestSan FranciscoLos Gatos176992.75
2004WestSan FranciscoSan Jose395330.25
2005CentralChicagoChicago131263.00
2005CentralChicagoDetroit136903.25
2005CentralMinneapolisMilwaukee173844.25
2005CentralMinneapolisMinneapolis132125.75
2005NorthBostonBoston189761.00
2005NorthBostonHartford135879.50
2005NorthNew YorkNew York171749.75
2005NorthNew YorkPhiladelphia171759.50
2005SouthAtlantaAtlanta229615.05
2005SouthAtlantaMiami234458.90
2005SouthNew OrleansHouston186394.25
2005SouthNew OrleansNew Orleans190441.75
2005WestLos AngelesLos Angeles228433.00
2005WestLos AngelesPhoenix197044.50
2005WestSan FranciscoCupertino196439.75
2005WestSan FranciscoLos Gatos175048.75
2005WestSan FranciscoSan Jose398829.10

Lồng nhau các kết quả đã nhóm lại: mệnh đề GROUP BY

Khi tên của một vài cột xuất hiện trong một mệnh đề GROUP BY, bảng kết quả được chia thành các nhóm bên trong các nhóm. Ví dụ, nếu bạn chỉ rõ các tên cột và nhóm theo năm, vùng, và hạt trong mệnh đề GROUP BY, các số trả về được phân chia theo năm, mỗi năm được phân chia theo vùng và mỗi vùng được phân chia theo hạt.

Trong ví dụ trên, các mục được nhóm lại theo năm, vùng, hạt và thành phố tạo ra một mức độ chi tiết tốt hơn. Nếu bạn bỏ qua cột thành phố, tập hợp kết quả sẽ ngắn hơn và ở một mức độ chi tiết kém hơn.

		SELECT year, region, district, SUM(dollars) AS sales
		FROM aroma.store a, aroma.sales b,aroma.product c,
		     aroma.period d, aroma.market e
		WHERE   a.storekey = b.storekey
		    AND c.prodkey = b.prodkey
		    AND c.classkey = b.classkey
		    AND d.perkey = b.perkey
		    AND e.mktkey = a.mktkey
		GROUP BY year, region, district
		ORDER BY year, region, district;

Kết quả

YearRegionDistrictSales
2004CentralChicago268486.25
2004CentralMinneapolis172321.50
2004NorthBoston253843.75
2004NorthNew York354130.75
2004SouthAtlanta450866.20
2004SouthNew Orleans376906.00
2004WestLos Angeles412002.45
2004WestSan Francisco752411.75
2005CentralChicago268166.25
2005CentralMinneapolis305970.00
2005NorthBoston325640.50
2005NorthNew York343509.25
2005SouthAtlanta464073.95
2005SouthNew Orleans376836.00
2005WestLos Angeles425477.50
2005WestSan Francisco770317.60
2006CentralChicago64190.00
2006CentralMinneapolis76417.50
2006NorthBoston78494.25
2006NorthNew York91840.25
2006SouthAtlanta106912.20
2006SouthNew Orleans93156.75
2006WestLos Angeles103876.15
2006WestSan Francisco192503.30

Cú pháp của mệnh đề GROUP BY

		SELECT column name(s)
		FROM table name(s)
		[WHERE search_condition]
		[GROUP BY group_list]
		[ORDER BY order_list];
group_listlà một danh sách các tên cột hoặc trong số các tên cột hoặc thuộc các bảng được liệt kê trong mệnh đề FROM. Tất cả các cột không được gộp lại trong số các tên cột phải xuất hiện ở group_list.

Về truy vấn

Ví dụ truy vấn lấy ra doanh số hàng năm của tất cả các sản phẩm cho mỗi thành phố trong năm 2004 và 2005. Các số doanh thu vừa được nhóm lại và vừa được sắp xếp thứ tự theo năm, vùng, hạt, và thành phố.

mũi tênChú ý quan trọng: Các thành phố nói đến trong truy vấn này là các thành phố nơi đặt cửa hàng, như được định nghĩa trong bảng Store, không phải các thành phố được định nghĩa như là hq_cities trên bảng Market.


OR so với UNION

Câu hỏi

Doanh thu tổng cộng trong tuần 52 của năm 2005 của tất cả cửa hàng của Aroma được phân loại "Trung bình" là bao nhiêu ? Các tổng số trong cùng giai đoạn cho các cửa hàng "Lớn" là bao nhiêu?

Ví dụ truy vấn với điều kiện OR

		SELECT store_name AS store, store_type AS size,
		       state, SUM(dollars) AS sales
		FROM     aroma.period p
		    JOIN aroma.sales s ON p.perkey = s.perkey
		    JOIN aroma.store r ON r.storekey = s.storekey
		WHERE   (store_type = 'Medium'
		    OR  store_type = 'Large')
		    AND year = 2005
		    AND week = 52
		GROUP BY store_name, store_type, state
		ORDER BY 2, 1;

Ví dụ truy vấn UNION

		SELECT store_name AS store, store_type AS size,
		       state, SUM(dollars) AS sales
		FROM     aroma.period p
		    JOIN aroma.sales s ON p.perkey = s.perkey
		    JOIN aroma.store r ON r.storekey = s.storekey
		WHERE   store_type = 'Medium'
		    AND year = 2005
		    AND week = 52
		GROUP BY store_name, store_type, state

		UNION

		SELECT store_name AS store, store_type AS size,
		       state, SUM(dollars)
		FROM     aroma.period p
		    JOIN aroma.sales s ON p.perkey = s.perkey
		    JOIN aroma.store r ON r.storekey = s.storekey
		WHERE   store_type = 'Large'
		    AND year = 2005
		    AND week = 52
		GROUP BY store_name, store_type, state
		ORDER BY 2, 1;

Hai truy vấn; cùng kết quả

StoreSizeStateSales
Beaches BrewLargeCA2908.80
Miami EspressoLargeFL4582.00
Olympic Coffee CompanyLargeGA3732.50
San Jose Roasting CompanyLargeCA3933.15
Beans of BostonMediumMA3772.75
Cupertino Coffee SupplyMediumCA2893.00
Java Judy'sMediumAZ3011.25
Moulin Rouge RoastingMediumLA3972.00
Texas TeahouseMediumTX3382.75

Kết hợp các tập hợp kết quả: UNION

Bạn có thể sử dụng các toán tử UNION, EXCEPT, và INTERSECT để kết hợp đầu ra của hai hoặc nhiều biểu thức truy vấn vào trong chỉ một tập hợp các hàng và các cột. Máy chủ đánh giá độc lập từng biểu thức truy vấn, rồi kết hợp đầu ra, hiển thị các tiêu đề cột từ biểu thức đầu tiên. Máy chủ loại trừ các hàng kết quả giống nhau trừ khi bạn chỉ rõ từ khóa ALL.

Hợp của hai tập hợp
Hợp

UNION, INTERSECT, EXCEPT

		query_expression UNION | INTERSECT | EXCEPT [ALL] query_expression
		[ORDER BY order_list];
query_expressionBất kỳ biểu thức truy vấn nào có nối bảng hoặc không nối bảng như định nghĩa trong SQL Reference Guide.

Nếu mệnh đề ORDER BY được sử dụng, các giá trị phải tham chiếu các cột từ danh sách lựa chọn của biểu thức truy vấn đầu tiên. Việc sử dụng các tên cột từ biểu thức truy vấn thứ hai sẽ dẫn đến một lỗi.

Về truy vấn

Cùng một câu hỏi nghiệp vụ giống nhau có thể được trả lời hoặc bằng cách chỉ rõ một điều kiện OR trong một lệnh SELECT đơn hoặc bằng cách kết hợp hai biểu thức truy vấn với toán tử UNION.

Việc sử dụng từ nối OR dễ dàng hơn trong ví dụ đơn giản này, nhưng trong một số trường hợp phép toán UNION cải thiện hiệu năng truy vấn. Ví dụ, giả sử truy vấn của bạn yêu cầu truy nhập dữ liệu trong hai bảng sự kiện lớn. Phép nối ngoài mà một truy vấn đơn cần đến có thể đòi hỏi nhiều xử lý hơn so với khi sử dụng một phép toán UNION để kết hợp các kết quả của hai biểu thức truy vấn.

Mệnh đề ORDER BY tham chiếu đến các vị trí cột, không phải các tên cột, được định nghĩa trong danh sách lựa chọn của biểu thức truy vấn đầu tiên:

		ORDER BY 1, 2

Các lưu ý về cách dùng

Các truy vấn UNION, INTERSECT, và EXCEPT phải đối xứng; nghĩa là, số các cột và thứ tự của chúng phải giống nhau trong các danh sách lựa chọn trên cả hai bên của toán tử UNION. Các cột tương ứng phải có kiểu dữ liệu giống nhau, hoặc có thể so sánh được, mặc dù chúng có thể có tên khác.

Nhiều toán tử UNION, INTERSECT, và EXCEPT có thể được sử dụng trong chỉ một lệnh đơn; các phép toán được đánh giá từ trái sang phải trừ khi bạn chỉ rõ mức ưu tiên bằng các dấu ngoặc.

UNION có ích khi kết hợp các bảng có kiểu dữ liệu giống nhau, tuy các tên cột khác nhau. Trong trường hợp như vậy, hành vi mặc định là cho tập hợp kết quả sử dụng các tên cột từ bảng đầu tiên.


UNION so với UNION ALL

Hàm UNION bao gồm một lệnh DISTINCT bên trong. Vì thế, các bản đúp bị loại bỏ và chỉ một thể hiện của mỗi giá trị được hiển thị. Nhiều câu hỏi kinh doanh có thể tận dụng được hành vi này.

Câu hỏi

Cung cấp một danh sách của tất cả các thành phố trong đó chúng ta có các cơ sở kinh doanh (có nghĩa là các cửa hàng), các trụ sở chính hay cả hai. Chỉ liệt kê mỗi thành phố một lần.

Ví dụ truy vấn (UNION)

		SELECT city
		FROM aroma.store
		UNION
		SELECT hq_city AS city
		FROM aroma.market;

Kết quả

CITY
Atlanta
Boston
Chicago
...
Phoenix
San Francisco
San Jose

Tuy nhiên, vào dịp khác bạn có thể muốn lặp lại các giá trị xuất hiện hơn một lần trong danh sách của các đối tượng được kết hợp lại. Ví dụ, bạn có thể muốn đếm số tổng của các đối tượng trong mỗi trường hợp.

Câu hỏi

Cung cấp một danh sách của tất cả các thành phố trong đó chúng ta có các cơ sở kinh doanh (có nghĩa là các cửa hàng), các trụ sở chính hay cả hai. Liệt kê mỗi thành phố một lần cho mỗi trường hợp ở đó có cửa hàng hay trụ sở chính.

Ví dụ truy vấn (UNION ALL)

		SELECT city
		FROM aroma.store
		UNION ALL
		SELECT hq_city AS city
		FROM aroma.market
		ORDER BY city;

Kết quả

CITY
Atlanta
Atlanta
Boston
Boston
Chicago
Chicago
Cupertino
...
San Jose
San Jose
San Jose

Trong ví dụ này, tất cả các thành phố Atlanta, Boston, Chicago, và San Jose có nhiều hơn một cơ sở, trong khi Cupertino chỉ có một.


Phép toán INTERSECT

Câu hỏi

Các sản phẩm chè chủ yếu nào được bán ra trong dịp khuyến mại ở San Jose trong năm 2006 mà cũng đã được bán ra trong dịp khuyến mại ở New Orleans vào năm 2005? Các loại khuyến mại nào được thực hiện với các sản phẩm đó?

Ví dụ truy vấn

		SELECT prod_name AS tea_name, promo_desc
		FROM     aroma.class c
		    JOIN aroma.product d ON c.classkey = d.classkey
		    JOIN aroma.sales s ON d.prodkey = s.prodkey
		        AND d.classkey = s.classkey
		    JOIN aroma.store t ON t.storekey = s.storekey
		    JOIN aroma.period p ON p.perkey = s.perkey
		    JOIN aroma.promotion n ON n.promokey = s.promokey
		WHERE   city = 'San Jose'
		    AND year = 2006
		    AND class_desc LIKE 'Bulk tea%'
		
		INTERSECT
		
		SELECT prod_name AS tea_name, promo_desc
		FROM     aroma.class c
		    JOIN aroma.product d ON c.classkey = d.classkey
		    JOIN aroma.sales s ON d.prodkey = s.prodkey
		        AND d.classkey = s.classkey
		    JOIN aroma.store t ON t.storekey = s.storekey
		    JOIN aroma.period p ON p.perkey = s.perkey
		    JOIN aroma.promotion n ON n.promokey = s.promokey
		WHERE   city = 'New Orleans'
		    AND year = 2005
		    AND class_desc LIKE 'Bulk tea%'
		    AND promo_desc NOT LIKE 'No promo%'
		ORDER BY promo_desc;

Kết quả

Tea_NamePromo_Desc
Irish BreakfastAroma catalog coupon
Special TipsAroma catalog coupon
Darjeeling SpecialStore display
Darjeeling SpecialTemporary price reduction
Gold TipsTemporary price reduction

Tìm ra các hàng chung: INTERSECT

Bạn có thể sử dụng toán tử INTERSECT để trả về chỉ các hàng nào là chung cho các kết quả được hai hoặc nhiều biểu thức truy vấn trả về.

Giao của hai tập hợp
Giao

Về truy vấn

Ví dụ truy vấn tìm ra phần giao nhau của hai biểu thức truy vấn, một biểu thức trả về một danh sách của các sản phẩm chè chủ yếu được bán ra trong dịp khuyến mại ở San Jose năm 2006 và một biểu thức khác trả về một danh sách tương tự cho New Orleans vào 2005. Toán tử INTERSECT loại trừ tất cả các hàng không tìm thấy trong cả hai tập hợp kết quả sơ bộ.


Phép toán EXCEPT

Câu hỏi

Tổng lợi tức của năm 2005 cho các cửa hàng trong các thành phố của California không được định nghĩa như các thành phố HQ trên bảng Market là bao nhiêu?

Ví dụ truy vấn

		SELECT x.city, store_name, SUM(dollars) AS sales_05
		FROM
		    (SELECT city
		     FROM aroma.store
		     WHERE state='CA'
		
		     EXCEPT
		
		     SELECT hq_city
		     FROM aroma.market
		     WHERE hq_state = 'CA') AS x(city)
		
		     JOIN aroma.store t ON x.city = t.city
		     JOIN aroma.sales s ON s.storekey = t.storekey
		     JOIN aroma.period p ON p.perkey = s.perkey
		WHERE year = 2005
		GROUP BY x.city, store_name
		ORDER BY 3 DESC;

Kết quả

CityStore_NameSales_05
CupertinoCupertino Coffee Supply196,439.75
Los GatosRoasters, Los Gatos175,048.75

EXCEPT: tìm phần loại trừ giữa hai tập hợp kết quả

Toán tử EXCEPT (loại trừ) tìm ra phần loại trừ (hoặc sự khác nhau) giữa các kết quả của hai biểu thức truy vấn. Ví dụ, phép toán EXCEPT có thể so sánh các danh sách sản phẩm được bán tại hai cửa hàng, loại trừ tất cả các sản phẩm được bán tại cả hai cửa hàng, và chỉ giữ lại các sản phẩm nào được bán chỉ tại cửa hàng nói trong biểu thức truy vấn đầu tiên.

Phép loại trừ giữa hai tập hợp
Loại trừ

Về truy vấn

Trong ví dụ truy vấn, chức năng của toán tử EXCEPT là chọn các thành phố nào của California đã được định nghĩa trong cột City của bảng Store nhưng không phải trong cột Hq_City của bảng Market.

Truy vấn này sử dụng một truy vấn phụ trong mệnh đề FROM để tạo ra một bảng dẫn xuất của các thành phố có thể nối với các bảng Sales, Store, và Period Bảng dẫn xuất từ truy vấn phụ được đặt cho một tên tương quan và một tên cột: x(city)

Bảng dẫn xuất này có thể được nối với bảng Store sử dụng phép nối qua cột City .

Các lưu ý về cách dùng

Để kiểm tra kết quả của phép toán EXCEPT, bạn có thể chạy truy vấn phụ trong ví dụ này như một truy vấn riêng biệt:

		(SELECT city
		 FROM aroma.store
		 WHERE state = 'CA'
		 EXCEPT
		 SELECT hq_city as city
		 FROM aroma.market
		 WHERE hq_state = 'CA');

Kết quả

City
Cupertino
Los Gatos

Để có nhiều ví dụ hơn về các truy vấn phụ, xem Phần 5 của loạt bài này.


Tóm tắt

Tóm tắt

Hướng dẫn này mô tả:

  • Cách nối các bảng
  • Cách kết hợp các kết quả của hai biểu thức truy vấn độc lập khi sử dụng các toán tử UNION, INTERSECT, và EXCEPT

Nó cũng bao trùm các chủ đề sau đây:

  • Các tên và các lược đồ bảng
  • Mệnh đề ORDER BY
  • Các hàm tập hợp SUM, AVG, MAX, MIN, COUNT, và
  • Mệnh đề GROUP BY

Tải về

Mô tảTênKích thước
Cơ sở dữ liệu AromaAroma_Data.zip1MB

Tài nguyên

Học tập

Lấy sản phẩm và công nghệ

  • Tải xuống DB2 Express-C, một phiên bản DB2 Express miễn phí để phát triển và triển khai các ứng dụng.

Thảo luận

Bình luận

developerWorks: Đăng nhập

Các trường được đánh dấu hoa thị là bắt buộc (*).


Bạn cần một ID của IBM?
Bạn quên định danh?


Bạn quên mật khẩu?
Đổi mật khẩu

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Ở lần bạn đăng nhập đầu tiên vào trang developerWorks, một hồ sơ cá nhân của bạn được tạo ra. Thông tin trong bản hồ sơ này (tên bạn, nước/vùng lãnh thổ, và tên cơ quan) sẽ được trưng ra cho mọi người và sẽ đi cùng các nội dung mà bạn đăng, trừ khi bạn chọn việc ẩn tên cơ quan của bạn. Bạn có thể cập nhật tài khoản trên trang IBM bất cứ khi nào.

Thông tin gửi đi được đảm bảo an toàn.

Chọn tên hiển thị của bạn



Lần đầu tiên bạn đăng nhập vào trang developerWorks, một bản trích ngang được tạo ra cho bạn, bạn cần phải chọn một tên để hiển thị. Tên hiển thị của bạn sẽ đi kèm theo các nội dung mà bạn đăng tải trên developerWorks.

Tên hiển thị cần có từ 3 đến 30 ký tự. Tên xuất hiện của bạn phải là duy nhất trên trang Cộng đồng developerWorks và vì lí do an ninh nó không phải là địa chỉ email của bạn.

Các trường được đánh dấu hoa thị là bắt buộc (*).

(Tên hiển thị cần có từ 3 đến 30 ký tự)

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Thông tin gửi đi được đảm bảo an toàn.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=70
Zone=Information Management, SOA và dịch vụ Web
ArticleID=972929
ArticleTitle=Hướng dẫn SQL & XQuery cho IBM DB2, Phần 3: Phép nối và phép hợp SQL
publish-date=05202009