WITH đưa ra một cách thức để viết các truy vấn con để sử dụng trong một truy vấn SELECT lớn hơn.
Các truy vấn con, chúng thường được tham chiếu tới như là các Biểu thức Bảng Chung - CTE (Common Table Expressions), có thể được xem như việc định nghĩa các bảng tạm thời đang tồn tại
chỉ vì truy vấn này. Người ta sử dụng tính năng này là để chia các truy vấn phức tạp thành các phần đơn giản hơn. Một ví dụ là:
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales FROM orders
GROUP BY region ), top_regions AS (
SELECT region FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) )
SELECT region, product,
SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders
WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
nó hiển thị tổng bán hàng theo từng sản phẩm chỉ trong các khu vực bán hàng hàng đầu. Ví dụ này có thể được viết mà không có WITH, nhưng chúng ta có thể đã cần tới 2 mức lồng nhau các lệnh
SELECT con. Dễ dàng hơn để tuân theo cách này.
Trình sửa đổi tùy chọn RECURSIVE làm thay đổi WITH từ chỉ sự thuận tiện về cú pháp trong một tính năng hoàn thành các điều mà nếu khác đi thì không có khả năng trong SQL tiêu chuẩn. Sử dụng
RECURSIVE, một truy vấn WITH có thể tham chiếu tới đầu ra của riêng nó. Một ví dụ rất đơn giản là truy vấn này để tính tổng các số nguyên từ 1 tới 100:
WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL
SELECT n+1 FROM t WHERE n < 100 )
SELECT sum(n) FROM t;
Dạng chung của một truy vấn đệ qui WITH luôn là một khoản không đệ qui, rồi UNION (hoặc UNION ALL), rồi một khoản đệ qui, nơi mà chỉ khoản đệ qui có thể có một tham chiếu tới đầu ra của riêng truy vấn đó. Một truy vấn như vậy được thực thi như sau:
Đánh giá truy vấn đệ qui
1. Hãy đánh giá khoản không đệ qui. Đối với UNION (nhưng không với UNION ALL), hãy bỏ các hàng đúp bản. Hãy đưa vào tất cả các hàng còn lại trong kết quả của truy vấn đệ qui, và cũng đặt chúng vào một bảng làm việc tạm thời.
2. Miễn là bảng làm việc không rỗng, hãy lặp lại các bước:
a) Đánh giá khoản đệ qui, thay thế các nội dung của bảng làm việc đối với tự tham chiếu đệ qui. Đối với UNION (nhưng không với UNION ALL), hãy bỏ các hàng đúp bản và các hàng đúp bất kỳ hàng kết quả nào trước đó. Đưa vào tất cả các hàng còn lại vào trong kết quả của truy vấn đệ qui, và cũng đặt chúng vào một bảng trung gian tạm.
b) Thay thế các nội dung của bảng làm việc bằng các nội dung của bảng trung gian, rồi làm
rỗng bảng trung gian.
Lưu ý: Nói một cách nghiêm ngặt, qui trình này là lặp đi lặp lại không phải sự đệ qui, mà
RECURSIVE là thuật ngữ được ủy ban các tiêu chuẩn SQL lựa chọn.
Trong ví dụ ở trên, bảng làm việc chỉ có 1 hàng duy nhất trong từng bước, và nó lấy các giá trị từ 1 đến 100 theo các bước kế tiếp. Trong bước thứ 100, không có đầu ra nào vì mệnh đề WHERE, và vì thế truy vấn kết thúc.
Các truy vấn đệ qui thường được sử dụng để làm việc với các dữ liệu kế thừa hoặc có cấu trúc hình cây. Một ví dụ hữu dụng là truy vấn này thấy tất cả các phần con trực tiếp và gián tiếp của một sản phẩm, biết rằng chỉ một bảng chỉ ra được những chèn thêm ngay tức thì:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = ’our_product’
UNION ALL
SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part )
SELECT sub_part, SUM(quantity) as total_quantity FROM included_parts
GROUP BY sub_part
Khi làm việc với các truy vấn đệ qui, điều quan trọng phải chắc chắn rằng phần đệ qui của truy vấn cuối cùng sẽ không trả về bộ số liệu, nếu không thì truy vấn sẽ lặp vô tận. Đôi khi, việc sử dụng
UNION thay cho UNION ALL có thể hoàn tất được điều này bằng việc bỏ các hàng mà đúp bản các hàng đầu ra trước đó. Tuy nhiên, thường thì một chu kỳ không liên quan tới các hàng đầu ra mà hoàn toàn đúp bản: có thể là cần thiết để kiểm tra chỉ một hoặc một ít các trường để thấy liệu điểm y hệt có đạt được trước hay không. Phương pháp tiêu chuẩn cho việc điều khiển các tình huống như vậy là để tính toán bất kỳ mảng giá trị nào đã được thăm viếng rồi. Ví dụ, xem xét truy vấn sau đây tìm kiếm đồ họa một bảng bằng việc sử dụng một trường liên kết:
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1
FROM graph g UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg
WHERE g.id = sg.link )
SELECT * FROM search_graph;
Truy vấn này sẽ lặp nếu các mối quan hệ liên kết có các chu kỳ. Vì chúng ta yêu cầu một đầu ra
“sâu”, nên chỉ việc thay đổi UNION ALL thành UNION cũng có thể không loại trừ được việc lặp. Thay vào đó chúng ta cần nhận thức được liệu chúng ta đã tới được hàng y hệt một lần nữa hay chưa trong khi tuân theo một đường các liên kết đặc biệt. Chúng ta thêm 2 cột path và cycle vào truy vấn lặp - dễ hỏng:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1, ARRAY[g.id],
false FROM graph g UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id,
g.id = ANY(path)
FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle )
SELECT * FROM search_graph;
Ngoài việc ngăn các chu kỳ, giá trị mảng thường hữu dụng theo quyền của riêng nó như việc đại diện cho “đường” (“path”) được lấy để với tới được bất kỳ hàng đặc biệt nào.
Trong trường hợp chung nơi mà nhiều hơn một trường cần phải được kiểm tra để nhận thức được một chu kỳ, hãy sử dụng một mảng các hàng. Ví dụ, nếu chúng ta cần so sánh các trường f1 và f2:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1,
ARRAY[ROW(g.f1, g.f2)], false
FROM graph g UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2),
ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle )
SELECT * FROM search_graph;
Mẹo: Bỏ qua cú pháp ROW() trong trường hợp chung nơi mà chỉ một trường cần phải được kiểm tra để nhận ra được một chu kỳ. Điều này cho phép một mảng đơn giản hơn là một mảng dạng tổng hợp sẽ được sử dụng, giành được sự hiệu quả.
Mẹo: Thuật toán đánh giá truy vấn đệ qui tạo ra đầu ra của nó theo trật tự tìm kiếm theo độ rộng trước. Bạn có thể hiển thị các kết quả theo trật tự tìm kiếm độ sâu trước bằng cách làm cho truy vấn vòng ngoài ORDER BY thành một cột “đường” (“path”) được xây theo cách này.
Một mẹo hữu dụng cho việc kiểm thử các truy vấn khi bạn không chắc chắn nếu chúng có thể lặp là hãy đặt một LIMIT vào truy vấn cha. Ví dụ, truy vấn này có thể lắp bất tận mà không có LIMIT:
WITH RECURSIVE t(n) AS ( SELECT 1
UNION ALL
SELECT n+1 FROM t )SELECT n FROM t LIMIT 100;
Điều này làm việc vì sự triển khai PostgreSQL chỉ đánh giá càng nhiều hàng của một truy vấn WITH
như thực sự được truy vấn cha lấy. Sử dụng mẹo này trong sản xuất không được khuyến cáo, vì các hệ thống khác có thể làm việc khác nhau. Hơn nữa, nó thực sự không làm việc nếu bạn để truy vấn vòng ngoài sắp xếp các kết quả truy vấn đệ qui hoặc liên kết chúng với một số bảng khác.
Một đặc tính hữu dụng của các truy vấn WITH là chúng chỉ được đánh giá một lần cho từng sự thực
thi của truy vấn cha, thậm chí nếu chúng được tham chiếu tới nhiều hơn một lần đối với truy vấn cha hoặc các truy vấn anh em WITH. Vì thế, những tính toán đắt giá cần thiết ở nhiều nơi có thể được đặt trong một truy vấn WITH để tránh công việc dư thừa. Ứng dụng có khả năng khác là để ngăn ngừa nhiều đánh giá các hàm không mong muốn với các hiệu ứng phụ. Tuy nhiên, mặt kia của đồng xu này là trình tối ưu hóa ít có khả năng hơn để đẩy những hạn chế từ truy vấn cha xuống vào trong một truy vấn WITH so với một truy vấn con (phụ) thông thường. Với truy vấn WITH thường sẽ được đánh giá như được nêu, mà không ép các hàng mà truy vấn cha có thể bỏ sau đó. (Mà, như được nhắc tới ở trên, sự đánh giá có thể dừng sớm nếu (các) tham chiếu cho truy vấn chỉ đòi hỏi một số lượng hạn chế các hàng).