Phép Nối, Các Loại Phép Nối, Phép Nối Theo Chuẩn SQL-92, Mệnh đề ...

Bài viết sau đây sẽ giới thiệu tới các bạn về Phép nối, Các loại phép nối, Phép nối theo chuẩn SQL-92, Mệnh đề GROUP BY, Truy vấn con trong SQL

1. Phép nối

Khi cần thực hiện một yêu cầu truy vấn dữ liệu từ hai hay nhiều bảng, ta phải sử d ụng đến phép nối. Một câu lệnh nối kết hợp các dòng dữ liệu trong các bảng khác nhau lại theo một hoặc nhiều điều kiện nào đó và hiển thị chúng trong kết quả truy vấn.

Ví dụ: Để tìm ra khách hàng có mã là 3 đã đặt hàng trong những ngày nào thì câu truy vấn như sau:

select c.CUSTOMERNAME, o.ORDERDATE from customers c, orders o where c.customerid = o.customerid

Tìm kiếm trong bảng Orders các dòng có giá trị trường CUSTOMERID là 3 và cho các dòng này vào kết quả truy vấn.

Như vậy để thực hiện yêu cầu truy vấn, chúng ta phải thực hiện phép kết nối giữa hai bảng Customers và Orders với điều kiện kết nối là CUSTOMERID của bảng CUSTOMERS bằng với CUSTOMERID của bảng ORDERS.

Phép nối là cơ sở để thực hiện các yêu cầu truy vấn dữ liệu liên quan đến nhiều bảng. Một câu lệnh nối thực hiện lấy các dòng dữ liệu trong các bảng tham gia truy vấn, so sánh giá trị của các dòng này trên một hoặc nhiều cột được chỉ định trong điều kiện nối và kết hợp các dòng thoả mãn điều kiện thành những dòng trong kết quả truy vấn.

Để thực hiện được một phép nối, cần phải xác định được những yếu tố sau:

Những cột nào cần hiển thị trong kết quả truy vấn

Những bảng nào có tham gia vào truy vấn.

Điều kiện để thực hiện phép nối giữa các bảng dữ liệu l à gì

Trong các yếu tố kể trên, việc xác định chính xác điều kiện để thực hiện phép nối giữa các bảng đóng vai trò quan trọng nhất. Trong đa số các trường hợp, điều kiện của phép nối được xác định nhờ vào mối quan hệ giữa các bảng cần phải truy xuất dữ liệu. Thông th ường, đó là điều kiện bằng nhau giữa khoá chính và khoá ngoài của hai bảng có mối quan hệ với nhau. Như vậy, để có thể đưa ra một câu lệnh nối thực hiện chính xác yêu cầu truy vấn dữ liệu đòi hỏi phải hiểu được mối quan hệ cũng như ý nghĩa của chúng giữa các bảng dữ liệu.

Một câu lệnh nối cũng được bắt đầu với từ khóa SELECT. Các cột đ ược chỉ định tên sau từ khoá SELECT là các cột được hiển thị trong kết quả truy vấn.  Việc sử dụng tên các cột trong danh sách chọn có thể là:

Tên của một số cột nào đó trong các bảng có tham gia vào truy vấn. Nếu tên cột trong các bảng trùng tên nhau thì tên cột phải được viết dưới dạng tên_bảng.tên_cột

Dấu sao (*) được sử dụng trong danh sách chọn khi cần hiển thị tất cả các cột của các bảng tham gia truy vấn.

Trong trường hợp cần hiển thị tất cả các cột của một bảng n ào đó, ta sử dụng cách viết:

tên_bảng.*

Mệnh đề FROM trong phép nối

Sau  mệnh  đề  FROM  của  câu lệnh  nối  là  danh  sách  tên  các  bảng  (hay  khung nhìn) tham gia vào truy vấn. Nếu ta sử dụng dấu * trong danh sách chọn th ì thứ tự của các bảng liệt kê sau FROM sẽ ảnh hưởng đến thứ tự các cột được hiển thị trong kết quả truy vấn.

Mệnh đề WHERE trong phép nối

Khi hai hay nhiều bảng được nối với nhau, ta phải chỉ định điều kiện để thực hiện   phép nối ngay sau mệnh đề WHERE. Điều kiện nối đ ược biểu diễn dưới dạng biểu thức logic so sánh giá trị dữ liệu giữa các cột của các bảng tham gia truy vấn.

Các toán tử so sánh dưới đây được sử dụng để xác định điều kiện nối

Phép toán	Ý ngh ĩa
=	B ằng
>	L ớn hơn
>=	L ớn hơn hoặc bằng
<	Nh ỏ hơn
<=	Nh ỏ hơn hoặc bằng
<>	Khác
!>	Không l ớn hơn
!<	Không nh ỏ hơn

2. Các loại phép nối

Phép nối bằng: Một phép nối bằng (equi-join) là một phép nối trong đó giá trị của các cột được sử dụng để nối được so sánh với nhau dựa trên tiêu chuẩn bằng và tất cả các cột trong các bảng tham gia nối đều được đưa ra trong kết quả.

Một dạng đặc biệt của phép nối bằng được sử dụng nhiều là phép nối tự nhiên (naturaljoin). Trong phép nối tự nhiên, điều kiện nối giữa hai bảng chính là điều kiện bằng giữa khoá ngoài và khoá chính của hai bảng; Và trong danh sách chọn của câu lệnh chỉ giữ lại một cột trong hai cột tham gia vào điều kiện của phép nối.

Ví dụ phép kết nối bằng:

select * from Customers c, Orders o where c.customerid = o.customerid

Ví dụ phép kết nối tự nhiên:

select c.CUSTOMERID, c.CUSTOMERNAME, c.BIRTHDAY, c.GENDER, c.ADDRESS, o.ORDERDATE from Customers c, Orders o where c.customerid = o.customerid

hoặc viết gọn:

select c.*, o.ORDERDATE from Customers c, Orders o where c.customerid = o.customerid

Trong phép kết nối bằng, trường CUSTOMERID xuất hiện hai lần. Sự d ư thừa được loại bỏ bằng cách sử dụng phép kết nối tự nhiên và việc chỉ định rõ các cột cột cần truy xuất.

Trong các câu lệnh nối, ngoài điều kiện của phép nối được chỉ định trong mệnh đề WHERE còn có thể chỉ định các điều kiện tìm kiếm dữ liệu khác (điều kiện chọn). Thông thường,  các  điều  kiện  này  được  kết hợp  với điều  kiện  nối  thông  qua  toán  tử AND.

Ví dụ:

select c.*, o.ORDERDATE from Customers c, Orders o where c.customerid = o.customerid and c.customerid = 3

Phép tự nối

Phép tự nối là phép nối mà trong đó điều kiện nối được chỉ định liên quan đến các cột của cùng một bảng. Trong trường hợp này, sẽ có sự xuất hiện tên của cùng một bảng nhiều lần trong mệnh đề FROM và do đó các bảng cần phải được đặt bí danh.

Ví dụ: Giả sử có yêu cầu tìm ra các khách hàng có nhiều hơn một đơn đặt hàng trong cùng ngày

select c1.CUSTOMERID, c1.CUSTOMERNAME from customers c1, customers c2, orders o1, orders o2 where c1.customerid = o1.customerid and c2.customerid = o2.customerid and c1.customerid = c2.customerid and o1.orderdate = o2.orderdate and o1.orderid <> o2.orderid

Câu truy vấn được giải thích như sau: Lần lượt lấy  ra các mã khách hàng, mã hóa đơn và ngày đặt hàng từ bảng c1, o1 đem so sánh lần lượt với các mã khách hàng, mã hóa đơn và ngày đặt hàng từ bảng c2, o2. Nếu việc so sánh hai tập hợp này thỏa điều kiện sau đây: mã khách hàng trùng nhau, ngày đặt hàng trùng nhau và có mã hóa đơn khác nhau thì thông tin khách hàng này được cho vào kết qua truy vấn.

Phép nối ngoài

Trong các phép nối đã đề cập ở trên, chỉ những dòng có giá trị trong các cột được chỉ định thoả mãn điều kiện kết nối mới được hiển thị trong kết quả truy vấn, và được gọi là phép nối trong (inner join) Theo một nghĩa nào đó, những phép nối này loại bỏ thông tin chứa trong những dòng không thoả mãn điều kiện nối.  Tuy nhiên, đôi khi ta cũng cần giữ lại những thông tin này bằng cách cho phép những dòng không thoả mãn điều kiện nối có mặt trong kết quả của phép nối. Để làm điều này, ta có thể sử dụng phép nối ngoài.

SQL cung cấp các loại phép nối ngoài sau đây:

Phép nối ngoài trái (ký hiệu: *=): Phép nối này hiển thị trong kết quả truy vấn tất cả các dòng dữ liệu của bảng nằm bên trái trong điều kiện nối cho dù những dòng này không thoả mãn điều kiện của phép nối

Phép nối ngoài phải (ký hiệu: =*): Phép nối này hiển thị trong kết quả truy vấn tất cả các dòng dữ liệu của bảng nằm bên phải trong điều kiện nối cho dù những dòng này không thoả điều kiện của phép nối.

Tuy nhiên trong SQL Server 2005 Express Edition không h ỗ trợ trực tiếp các phép nối *= và =*. Mặt khác trong các phiên bản SQL Server sắp tới các phép nối này sẽ hoàn toàn không được hỗ trợ. Do đó Microsoft khuyến cáo ng ười sử dụng dùng các phép nối LEFT JOIN, RIGHT JOIN. Các phép nối này sẽ được nói rõ trong phần dưới đây.

3. Phép nối theo chuẩn SQL-92

Chuẩn SQL2 (SQL-92) đưa ra một cách khác để biểu diễn cho phép nối, trong cách  biểu diễn  này,  điều  kiện  của  phép  nối  không  được  chỉ  định  trong  mệnh  đề WHERE mà được chỉ định ngay trong mệnh đề FROM của câu lệnh. Cách sử dụng phép nối n ày cho phép ta biểu diễn phép nối cũng như điều kiện nối được rõ ràng, đặc biệt là trong trường hợp phép nối được thực hiện trên ba bảng trở lên.

Phép nối trong

Điều kiện để thực hiện phép nối trong được chỉ định trong mệnh đề FROM theo cú pháp như sau: tên_bảng_1 [INNER] JOIN tên_bảng_2 ON điều_kiện_nối Ví dụ:

Phép nối ngoài

SQL2 cung cấp các phép nối ngoài sau đây:

Phép nối ngoài trái (LEFT OUTER JOIN)

Phép nối ngoài phải (RIGHT OUTER JOIN)

Phép nối ngoài đầy đủ (FULL OUTER JOIN)

Cũng tương tự như phép nối trong, điều kiện của phép nối ngoài cũng được chỉ định ngay trong mệnh đề FROM theo cú pháp:

tên_bảng_1 LEFT|RIGHT|FULL [OUTER] JOIN t ên_bảng_2 ON điều_kiện_nối

Ví dụ: Để tìm ra các khách hàng có đặt hàng thay vì sử dụng câu truy vấn sau:

select * customers c, orders o where c.customerid = o.orderid //Ta có thể sử dụng câu truy vấn sau: select * from customers c inner join orders o on c.customerid = o.customerid

Nếu phép nối ngoài trái hiển thị trong kết quả truy vấn cả những dòng dữ liệu không thoả điều kiện nối của bảng bên trái trong phép nối thì phép nối ngoài đầy đủ hiển thị trong kết quả truy vấn cả những dòng dữ liệu không thoả điều kiện nối của cả hai bảng tham gia v ào phép nối.

Ví dụ: Giả sử có CSDL như sau:

Phép nối ngoài phải:

select * from faculty f right join class c on f.facultyid = c.facultyid

Một đặc điểm nổi bật  của  SQL2   là  cho  phép  biểu  diễn  phép  nối   trên  nhiều bảng dữ liệu một cách rõ ràng. Thứ tự thực hiện phép nối giữa các bảng được xác định theo nghĩa kết quả của phép nối này được sử dụng trong một phép nối khác.

Ví dụ: Liệt kê tên các mặt hàng có trong đơn đạt hàng có mã là 1.

select i.ITEMNAME, o.ORDERDATE from (orders o inner join orderdetail od on o.orderid = od.orderid) inner join items i on od.itemid = i.itemid where o.orderid = 1

4. Mệnh đề GROUP BY

Ngoài  khả năng  thực hiện  các  yêu  cầu  truy  vấn  dữ  liệu  thông  thường  (chiếu, chọn, nối,…) như đã đề cập như ở các phần trước, câu lệnh SELECT còn cho phép thực hiện các thao tác truy vấn và tính toán thống kê trên dữ liệu.

Mệnh đề GROUP BY sử dụng trong câu lệnh SELECT nhằm phân hoạch các d òng dữ

liệu trong bảng thành các nhóm dữ liệu, và trên mỗi nhóm dữ liệu thực hiện tính toán các giá trị thống kê như tính tổng, tính giá trị trung bình,...

Các hàm gộp (aggregate functions) được sử dụng để tính giá trị thống kê cho toàn bảng hoặc trên mỗi nhóm dữ liệu. Chúng có thể được sử dụng như là các cột trong danh sách chọn của câu lệnh SELECT hoặc xuất hiện trong mệnh đề HAVING, nh ưng không được phép xuất

hiện trong mệnh đề WHERE

SQL cung cấp các hàm gộp dưới đây:

Hàm gộp		Chức năng
SUM([ALL| DISTINCT] biểu_thức)		Tính tổng các giá trị.
AVG([ALL| DISTINCT] biểu_thức)		Tính trung bình của các giá trị
COUNT([ALL|DISTINCT] biểu_thức)		Đếm số các giá trị trong biểu thức.
COUNT(*)	Đếm số các dòng được chọn.
MAX(biểu_thức)	Tính giá trị lớn nhất
MIN(biểu_thức)	Tính giá trị nhỏ nhất

Hàm SUM và AVG chỉ làm việc với các biểu thức số.

Hàm SUM, AVG, COUNT, MIN và MAX b ỏ qua các giá trị NULL khi tính toán.

Hàm COUNT(*) không bỏ qua các giá trị NULL.

Mặc định, các hàm gộp thực hiện tính toán thống kê trên toàn bộ dữ liệu. Trong trường hợp cần loại bỏ bớt các giá trị trùng nhau (chỉ giữ lại một giá trị), ta chỉ định thêm từ khoá DISTINCT ở trước biểu thức là đối số của hàm.

Thống kê trên toàn bộ dữ liệu

Khi cần tính toán giá trị thống kê trên toàn bộ dữ liệu, ta sử dụng các hàm gộp trong danh sách chọn của câu lệnh SELECT. Trong trường hợp này, trong danh sách chọn không được sử dụng bất kỳ một tên cột hay biểu thức nào ngoài các hàm gộp.

Ví dụ: Tính tuổi trung bình, tuổi nhỏ nhất và lớn nhất của các khách hàng

select min(year(getdate())-year(BIRTHDAY)) as MINAGE, max(year(getdate())-year(BIRTHDAY)) as MAXAGE, avg(year(getdate())-year(BIRTHDAY)) as AVGAGE from customers

Thống kê trên nhóm

Trong trường hợp cần thực hiện tính toán các giá trị thống k ê trên các nhóm dữ liệu, ta sử dụng mệnh đề GROUP BY để phân hoạch dữ liệu v ào trong các nhóm. Các hàm gộp được sử dụng sẽ thực hiện thao tác tính toán trên mỗi nhóm và cho biết giá trị thống kê theo các nhóm dữ liệu.

Ví dụ: Câu truy vấn sau cho biết số tổng số tiển khách hàng phải trả cho tất cả các lần đặt hàng 

select c.CUSTOMERID, c.CUSTOMERNAME, convert(varchar(20),cast(SUM(i.UNITPRICE*od.QUANTITY) as money),1) as SUMTOTAL from customers c inner join orders o on o.customerid = c.customerid inner join orderdetail od on o.orderid = od.orderid inner join items i on i.itemid = od.itemid group by c.customerid, c.customername

Nếu muốn hiện số tiền khách hàng phải trả cho từng đơn đặt hàng, chỉ cần thêm trường ORDERID vào mệnh đề group by.

select c.CUSTOMERID, c.CUSTOMERNAME, convert(varchar(20),cast(SUM(i.UNITPRICE*od.QUANTITY)as money),1) as SUMTOTAL from customers c inner join orders o on o.customerid = c.customerid inner join orderdetail od on o.orderid = od.orderid inner join items i on i.itemid = od.itemid group by c.customerid, c.customername, o.orderid

Lưu ý: Trong trường hợp danh sách chọn của câu lệnh SELECT có cả các h àm gộp và những biểu thức không phải là hàm gộp thì những biểu thức này phải có mặt đầy đủ trong mệnh đề GROUP BY, nếu không câu lệnh sẽ không hợp lệ.

Mệnh đề HAVING chỉ định điều kiện trong hàm gộp

Mệnh đề HAVING được sử dụng nhằm chỉ định điều kiện đối với các giá trị thống k ê được sản sinh từ các hàm gộp tương tự như cách thức mệnh đề WHERE thiết lập các điều kiện cho câu lệnh SELECT. Mệnh đề HAVING thường không thực sự có nghĩa nếu như không sử dụng kết hợp với mệnh đề GROUP BY. Một điểm khác biệt giữa HAVING v à WHERE là trong điều kiện của WHERE không được có các hàm gộp trong khi HAVING lại cho phép sử dụng các hàm gộp trong điều kiện của mình.

Ví dụ: Tìm ra các khách hàng có tổng số tiền phải thanh toán cho tất cả các lần đặt h àng lớn hơn 100 triệu.

select c.CUSTOMERID, c.CUSTOMERNAME, convert(varchar(20),cast(SUM(i.UNITPRICE*od.QUANTITY)as money),1) as SUMTOTAL from customers c inner join orders o on o.customerid = c.customerid inner join orderdetail od on o.orderid = od.orderid inner join items i on i.itemid = od.itemid group by c.customerid, c.customername having sum(i.UNITPRICE*od.QUANTITY) > 100000000

5. Truy vấn con (Subquery)

Truy vấn con là một câu lệnh SELECT được lồng vào bên trong một câu lệnh

SELECT, INSERT, UPDATE, DELETE hoặc bên trong một truy vấn con khác. Loại truy vấn này được sử dụng để biểu diễn cho những truy vấn trong đó điều kiện truy vấn dữ liệu cần phải sử dụng đến kết quả của một truy vấn khác.

Cú pháp của truy vấn con như sau:

(SELECT [ALL | DISTINCT] danh_sách_ch ọn

FROM danh_sách_bảng [WHERE   điều_kiện]

[GROUP BY    danh_sách_cột] [HAVING điều_kiện])

Khi sử dụng truy vấn con cần lưu ý một số quy tắc sau:

Một  truy  vấn  con  phải  được  viết  trong  cặp  dấu  ngoặc.  Trong  hầu  hết  các tr ường hợp, một truy vấn con thường phải có kết quả là một cột (tức là chỉ có duy nhất một cột  trong danh sách chọn).

Mệnh đề COMPUTE và ORDER BY không được phép sử dụng trong truy vấn con.

Các tên cột xuất hiện trong truy vấn con có thể là các cột của các bảng trong truy vấn

ngoài.

Một  truy  vấn  con  thường  được  sử  dụng  làm  điều  kiện  trong  mệnh  đề WHERE hoặc HAVING của một truy vấn khác.

Nếu truy vấn con trả về đúng một giá trị, nó có thể sử dụng nh ư là một thành phần bên trong một biểu thức (chẳng hạn xuất hiện trong một phép so sánh bằng)

Phép so sánh đối với với kết quả truy vấn con

Kết quả của truy vấn con có thể được sử dụng đề thực hiện phép so sánh số học với một biểu thức của truy vấn cha. Trong trường hợp này, truy vấn con được sử dụng dưới dạng:

WHERE biểu_thức phép_toán_số_học [ANY|ALL] (truy_vấn_con)

Trong đó phép toán số học có thể sử dụng bao gồm: =, <>, >, <, >=, <=; V à truy vấn con phải có kết quả bao gồm đúng một cột.

Ví dụ: Câu truy vấn sau đây tìm tên khách hàng có tuổi lớn nhất

select c.CUSTOMERNAME, c.ADDRESS from customers c where year(getdate()) - year(BIRTHDAY) = (select max(year(getdate()) - year(BIRTHDAY)) from customers)

Nếu truy vấn con trả về nhiều hơn một giá trị, việc sử dụng phép so sánh như trên sẽ không hợp lệ. Trong trường hợp này, sau phép toán so sánh phải sử dụng thêm lượng từ ALL hoặc ANY. Lượng từ ALL được sử dụng khi cần so sánh giá trị của biểu thức với tất cả các giá trị trả về trong kết quả của truy vấn con; ng ược lai, phép so sánh với lượng từ ANY có kết quả đúng khi chỉ cần một giá trị bất kỳ nào đó trong kết quả của truy vấn con thoả mãn điều kiện

Ví dụ:

Toán tử IN/NOT IN

Khi cần thực hiện phép kiểm tra giá trị của một biểu thức có xuất hiện (không xuất hiện) trong tập các giá trị của truy vấn con hay không, ta có thể sử dụng toán tử IN (NOT IN) nh ư sau:

WHERE biểu_thức [NOT] IN (truy_vấn_con)

Ví dụ:

Truy vấn con với EXISTS

Lượng từ EXISTS được sử dụng kết hợp với truy vấn con dưới dạng:

WHERE [NOT] EXISTS (truy_vấn_con)

Lượng từ EXISTS (tương ứng NOT EXISTS) trả về giá trị True (tương ứng False) nếu kết quả của truy vấn con có ít nhất một dòng (tương ứng không có dòng nào). Điều khác biệt của việc sử dụng EXISTS với hai cách đã nêu ở trên là trong danh sách chọn của truy vấn con có thể có nhiều hơn hai cột.

Ví dụ:

Truy vấn con và mệnh đề HAVING

Một truy vấn con có thể được sử dụng trong mệnh đề HAVING của một truy vấn khác. Trong trường hơp này, kết quả của truy vấn con được sử dụng để tạo nên điều kiện đối với các hàm gộp.

Tags: Sql 2005sql server 2005giáo trình sql servergiáo trình sql servertự học sql 2005tự học sql 2005tìm hiểu sql serversql serverPhép nốiCác loại phép nốiPhép nối theo chuẩn SQL-92Mệnh đề GROUP BYTruy vấn con (Subquery) trong SQL