Cách dùng SQL OFFSET

Câu trả lời nhanh: Cách dùng SQL OFFSET

Mệnh đề OFFSET trong SQL được dùng để bỏ qua một số lượng hàng cụ thể trước khi trả về các hàng trong tập kết quả. Nó thường được dùng kết hợp với mệnh đề LIMIT để phân trang kết quả.

SELECT column_names
FROM table_name
ORDER BY column_name
LIMIT number_of_rows OFFSET offset_value;

Trong truy vấn SQL trên, number_of_rows chỉ số hàng cụ thể cần trả về, trong khi offset_value cho biết số hàng cần bỏ qua trước khi trả về.

OFFSET hoạt động như thế nào

Mệnh đề OFFSET bỏ qua số hàng được chỉ định trong tập kết quả. Khi dùng với ORDER BY, nó đảm bảo tính nhất quán bằng cách bỏ qua các hàng theo một thứ tự xác định, thường được chỉ định bởi tên cột. Theo mặc định, ORDER BY trả về cột đã chỉ định theo thứ tự tăng dần.

Giả sử bạn muốn truy vấn từ bảng employees các cột sau: employee_id, first_name, last_name, phone_number và salary. Bạn cũng muốn loại trừ 9 hàng đầu tiên khỏi kết quả. Bạn sẽ dùng truy vấn SQL dưới đây.

-- Select all columns for employees, starting from the 10th row
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id
-- Skip the first 9 rows
OFFSET 9 ROWS;

Cần nhớ rằng do cột sắp xếp không duy nhất, bạn phải cẩn trọng để tránh bản ghi trùng lặp.

Kết hợp LIMIT và OFFSET

OFFSET thường được dùng cùng LIMIT để kiểm soát số hàng trả về và vị trí bắt đầu trả về các hàng.

Trong ví dụ dưới đây, mệnh đề LIMIT chỉ trả về 10 hàng tiếp theo sau khi bỏ qua 9 hàng đầu tiên.

-- Select all columns for employees, starting from the 10th row and fetching the next 10 rows
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id
-- Skip the first 9 rows
OFFSET 9
-- Fetch the next 10 rows
LIMIT 10;

Ứng dụng thực tế của OFFSET

SQL OFFSET được dùng rộng rãi để phân trang trong các ứng dụng web. Phân trang dữ liệu, nói rõ hơn, là kỹ thuật chia nhỏ dữ liệu lớn thành các phần nhỏ hơn, giúp người dùng duyệt qua các tập dữ liệu lớn một cách hiệu quả.

Tôi xem phân trang dữ liệu là một trong những ví dụ hay nhất về việc dùng OFFSET và LIMIT. Trong kỹ thuật này, các mệnh đề OFFSET và LIMIT khi dùng cùng nhau sẽ chia dữ liệu thành các phần có thể quản lý và hiển thị trên nhiều trang.

Điều này cải thiện trải nghiệm người dùng vì không ai bị choáng ngợp khi thấy một danh sách khổng lồ cùng lúc.

Các ứng dụng khác của OFFSET bao gồm:

1. Bỏ qua các bản ghi nhất định khi phân tích dữ liệu.
2. Chuẩn bị dữ liệu để xuất theo từng phần (khi dùng kết hợp với LIMIT).
3. Đơn giản là tập trung vào dữ liệu trong một phạm vi cụ thể, khi bạn biết mình đang tìm gì.

Hỗ trợ theo hệ quản trị cơ sở dữ liệu và các lựa chọn thay thế

Các cơ sở dữ liệu khác nhau hỗ trợ mệnh đề OFFSET và LIMIT, bao gồm MySQL, PostgreSQL và SQLite. Các phương pháp thay thế như FETCH có thể được dùng cho những hệ không hỗ trợ OFFSET và LIMIT, như SQL Server.

Ví dụ, truy vấn dưới đây (dùng LIMIT) sẽ trả về giá trị trong các cơ sở dữ liệu MySQL, SQLite và PostgreSQL nhưng không phải SQL Server.

-- Select all columns for employees, starting from the 10th row and fetching the next 10 rows
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id
-- Skip the first 9 rows
OFFSET 9
-- Fetch the next 10 rows
LIMIT 10;

Vì vậy, bạn nên dùng FETCH trong SQL Server để trả về kết quả tương tự bằng truy vấn dưới đây;

-- Select specific columns for employees, starting from the 10th row and fetching the next 10 rows
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Order by employee_id to ensure consistent ordering
ORDER BY employee_id 
-- Skip the first 9 rows (starting from the 10th row)
OFFSET 9 ROWS 
 -- Fetch the next 10 rows
FETCH NEXT 10 ROWS ONLY;

Kỹ thuật nâng cao: OFFSET và FETCH

Mệnh đề FETCH, dùng trong tiêu chuẩn SQL, có thể là lựa chọn nâng cao thay thế cho LIMIT. Trong khi OFFSET chỉ số hàng cần bỏ qua, FETCH cho biết số hàng cần trả về từ truy vấn.

-- Selecting columns from employees table
SELECT employee_id, first_name, last_name, phone_number, salary
FROM employees
-- Ordering the result set by employee_id for consistent pagination
ORDER BY employee_id
-- Skipping the first 20 rows in the result set
OFFSET 20 ROWS
-- Fetching the next 10 rows only after the offset
FETCH NEXT 10 ROWS ONLY;

Các kỹ thuật SQL nâng cao khác có trong khóa học Associate Data Analyst in SQL của chúng tôi.

Lưu ý về hiệu năng và các thực tiễn tốt nhất khác

Dùng OFFSET hiệu quả đòi hỏi hiểu tác động của nó đến hiệu năng truy vấn.

OFFSET có thể ảnh hưởng đến hiệu năng truy vấn, đặc biệt với các tập dữ liệu lớn. SQL Server xử lý bộ nhớ đệm và thực thi truy vấn hiệu quả, nhưng khi dùng OFFSET với các tập dữ liệu lớn, có thể cần các chiến lược tối ưu hóa bổ sung như dùng Common Table Expressions (CTE) hoặc bảng tạm. Do đó, bạn phải theo dõi hiệu năng truy vấn để xác định vấn đề và giải quyết các nút thắt cổ chai cản trở việc thực thi. Khóa học Intermediate SQL Server của DataCamp có một chương về biểu thức bảng chung và các chủ đề nâng cao khác.

Dưới đây là một số thực tiễn tốt nhất bổ sung mà tôi đã rút ra trong quá trình làm việc.

• Luôn dùng mệnh đề ORDER BY phù hợp. Cách tiếp cận này đảm bảo bản ghi được sắp xếp nhất quán.
• Cân nhắc lập chỉ mục và các kỹ thuật tối ưu hóa truy vấn. Phương pháp này giúp tối ưu hiệu năng, đặc biệt khi làm việc với cơ sở dữ liệu lớn.

Kết luận và học thêm

Thành thạo mệnh đề OFFSET khi kết hợp với LIMIT là điều quan trọng để quản lý dữ liệu hiệu quả. Hãy thử nghiệm các mệnh đề này trong truy vấn SQL của bạn để cải thiện hiệu năng và nâng cao trải nghiệm người dùng. Bạn có thể tìm hiểu thêm về mệnh đề OFFSET trong SQL qua các khóa học Intermediate SQL và SQL fundamentals của DataCamp.

Tương tự, bạn có thể học thêm về phân tích dữ liệu bằng SQL với khóa học Reporting in SQL. Ngoài ra, nếu bạn muốn chứng minh kỹ năng thực hành với các dự án SQL, tôi khuyến nghị lấy SQL Associate Certification để giúp bạn nổi bật trong các buổi phỏng vấn.