Như bài trước (p2) đã giới thiệu, hôm nay mình tiếp tục giới thiệu cho mọi người phần JOIN và Subquery

I. Các Loại JOIN Trong SQL Server

JOIN là phép kết nối dữ liệu từ nhiều bảng lại với nhau. Khi bạn cần truy vấn các cột dữ liệu từ nhiều bảng khác nhau để trả về trong cùng một tập kết quả, bạn cần dùng JOIN. Đây có lẽ là chức năng được dùng nhiều nhất khi lập trình T-SQL. Nó giúp tái hiện lại thông tin thế giới thực từ dữ liệu lưu trữ trong mô hình quan hệ. Như ở bài 1 khi thực hiện lấy total_price nhưng phải mapping data qua 12 bảng thì giờ sử dụng các câu lệnh join có thể đưa ra kết quả nhanh hơn. Bài toán Ví dụ 1: Bảng Users và Managers có cấu trúc như sau:

Có 3 hình thức để Join 2 bảng: INNER JOIN, OUTER JOIN, và CROSS JOIN

1. INNER JOIN (Hoặc JOIN)

INNER JOIN trả về kết quả là các bản ghi mà trường được join ở hai bảng khớp nhau, các bản ghi chỉ xuất hiện ở một trong hai bảng sẽ bị loại. Cú pháp :

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

-- Có thể thay INNER JOIN bởi JOIN -- Ý nghĩa và kết quả là như nhau.

SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;

Ví dụ với bài toán 1:

2. OUTER JOIN

OUTER JOIN nới lỏng hơn, lấy về các bản ghi có mặt trong cả hai bảng và cả các bản ghi chỉ xuất hiện ở một trong hai bảng. Kiểu JOIN này được chia làm hai loại:

FULL OUTER JOIN: kết quả gồm tất cả các bản ghi của cả hai bảng. Với các bản ghi chỉ xuất hiện trong một bảng thì các cột dữ liệu từ bảng kia được điền giá trị NULL.

Cú Pháp:

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name

• HALF OUTER JOIN (LEFT hoặc RIGHT): nếu bảng A LEFT OUTER JOIN với bảng B thì kết quả gồm các bản ghi có trong bảng A, với các bản ghi không có mặt trong bảng B thì các cột từ B được điền NULL. Các bản ghi chỉ có trong B mà không có trong A sẽ không được trả về. Cú pháp:

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Ví dụ bài toán 1:

3. CROSS JOIN

CROSS JOIN: mỗi bản ghi của bảng A được kết hợp với tất cả các bản ghi của bảng B, tạo thành một tích Đề-các giữa hai bảng (số bản ghi trả về bằng tích của số bản ghi trong hai bảng).

II. Truy vấn con trong SQL (SubQuery)

1. Khái niệm cơ bản:

Sub query trong SQL hoặc truy vấn nội bộ hoặc truy vấn Nested là truy vấn trong một truy vấn SQL khác và được nhúng trong mệnh đề WHERE.

Một sub query được sử dụng để trả về dữ liệu sẽ được sử dụng trong truy vấn chính như một điều kiện để hạn chế hơn nữa dữ liệu được truy xuất.

Sub query có thể được sử dụng với câu lệnh SELECT, INSERT, UPDATE, và DELETE cùng với các toán tử như: =, <,>,> =, <=, IN, BETWEEN, v.v …

Có một vài quy tắc mà Sub query phải tuân theo:

Sub query phải được đặt trong dấu ngoặc đơn. Một sub query có thể chỉ có một cột trong mệnh đề SELECT, trừ khi nhiều cột trong truy vấn chính cho sub query để so sánh các cột đã chọn của nó. Không thể sử dụng lệnh ORDER BY trong sub query, mặc dù truy vấn chính có thể sử dụng ORDER BY. Lệnh GROUP BY có thể được sử dụng để thực hiện chức năng giống như ORDER BY trong một sub query. Sub query trả về nhiều hơn một hàng chỉ có thể được sử dụng với toán tử nhiều giá trị như toán tử IN. Danh sách SELECT không được bao gồm bất kỳ tham chiếu nào đến các giá trị đánh giá BLOB, ARRAY, CLOB hoặc NCLOB. Một sub query không thể được chứa trực tiếp một chức năng set. Toán tử BETWEEN không thể được sử dụng với một sub query. Tuy nhiên, toán tử BETWEEN có thể được sử dụng trong sub query.

2. Các kiểu truy vấn con.

2.1: Truy vấn con không tương quan (subquery non-correlated)

Một truy vấn con không tương quan là truy vấn con độc lập với truy vấn bên ngoài. Truy vấn con không tương quan được thi hành thi hành đầu tiên và một lần duy nhất cho toàn bộ câu lệnh. Kết quả của truy vấn con được điền vào truy vấn bên ngoài, và cuối cùng thi hành truy vấn bên ngoài.

2.2: Truy vấn con tương quan (subquery correlated)

Truy vấn con tương quan không độc lập với truy vấn bên ngoài. Một truy vấn con tương quan là một truy vấn con sử dụng các giá trị từ truy vấn bên ngoài trong mệnh đề WHERE của nó. Quá trình thực hiện như sau: các truy vấn bên ngoài được thực hiện trước tiên và sau đó thi hành truy vấn con bên trong cho mỗi dòng kết quả của truy vấn bên ngoài.

3. Sub query với câu lệnh SELECT

Cú pháp:

SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])

Example: a - Lấy ra những "Reservation" có "total_price" > 100000.

b- Lấy ra những đơn hàng ở bảng orderdetails và đính kèm theo tên đơn hàng tương ứng ở bảng "products"

SELECT orderNumber, quantityOrdered, (SELECT productName FROM products WHERE productCode = o.productCode) AS productName FROM orderdetails o;

c- Lấy ra những reservation có total_price > TB của tất cả reservation.

4. Sub query với câu lệnh INSERT

Sub query cũng có thể được sử dụng với các câu lệnh INSERT. Câu lệnh INSERT sử dụng dữ liệu được trả về từ sub query để chèn vào một bảng khác. Dữ liệu đã chọn trong sub query có thể được sửa đổi bằng bất kỳ ký tự, ngày hoặc số chức năng. Cú pháp:

INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]

Example: Vì bảng User và Agent có cấu trúc như nhau nên có thể sap chép data như sau:

INSERT INTO agents SELECT * FROM users WHERE ID IN (SELECT ID FROM CUSTOMERS) ; Tương tự chúng ta có thể demo các câu lệnh tương tự như: Sub query với câu lệnh UPDATE

Có thể sử dụng sub query kết hợp với câu lệnh UPDATE. Một hoặc nhiều cột trong một bảng có thể được cập nhật khi sử dụng một sub query với câu lệnh UPDATE.

Cú pháp cơ bản như sau.

UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]

Sub query với câu lệnh DELETE

Sub query có thể được sử dụng kết hợp với câu lệnh DELETE như với bất kỳ câu lệnh nào khác đã đề cập ở trên.

Cú pháp cơ bản như sau.

DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]

III.Note :

Một số chú ý về multiple_row subquery:

1. Toán tử IN:

bằng 1 trong những giá trị trong danh sách Cẩn thận với NOT IN khi câu subquery có giá trị null. So sánh với null sẽ luôn trả về null

2. Toán tử ANY

> ANY: chỉ cần lớn hơn giá trị nhỏ nhất trong danh sách < ANY: chỉ cần nhỏ hơn giá trị lớn nhất trong danh sách = ANY: tương đương với IN

3. Toán tử ALL

> ALL: phải lớn hơn tất cả các giá trị trong danh sách < ALL: phải nhỏ hơn tất cả các giá trị trong danh sách

4. Toán tử EXISTS

Trả về true khi câu subquery trả về ít nhất 1 dòng.