some-common-mistakes-when-writing-sql

Returning too many columns

Lỗi này ta gặp khá thường xuyên và có thể dẫn đến những ảnh hưởng rất xấu.
Trước tiên, hãy xem nó gây hiệu ứng xấu trên ứng dụng Java như thế nào: Nếu bạn đang select * (sao) hoặc bộ 50 cột "mặc định".
Ngay cả khi ta không đọc dữ liệu từ bộ kết quả, nó vẫn được chuyển qua bộ nhớ và được trình điều khiển JDBC tải vào bộ nhớ. Điều đó khá lãng phí IO và bộ nhớ trong khi ta chỉ cần 2-3 cột trong số đó.
Điều này là hiển nhiên, nhưng hãy cẩn thận với các hiệu ứng xấu trong cách thức thực thi cơ sở dữ liệu: Những hiệu ứng này thực sự có thể tồi tệ hơn nhiều so với các hiệu ứng trên ứng dụng Java.
Cùng xem ví dụ sau:

SELECT *
FROM   customer_view c
JOIN   order_view o
  ON   c.cust_id = o.cust_id

Giả sử bảng order_view tổng hợp dữ liệu của các bảng như CUSTOMER_ADDRESS, ORDER_HISTORY, ORDER_SETTLEMENT,.... Khi đó truy vấn SELECT * sẽ trả về tất các các column từ bảng customer_view, order_view.
Số lượng column trả về có thể rất lớn, và đôi khi nhập nhằng nếu tên column ở 2 bảng trong phép join trùng tên. Trong khi nếu chỉ cần một vài column thì câu sql sau vừa đơn giản vừa tối ưu về mặt performance hơn.

SELECT c.first_name, c.last_name, o.amount
FROM   customer_view c
JOIN   order_view o
  ON   c.cust_id = o.cust_id

Using pre-ANSI 92 JOIN syntax

Tránh sử dụng điều kiện join trong các mệnh đề where như ví dụ dưới đây. Nếu câu truy vấn của chúng ta có khoảng 5 hoặc 7 bảng hoặc nhiều hơn thì sẽ khó follow hơn

SELECT c.first_name, c.last_name, o.amount
FROM   customer_view c,
       order_view o
WHERE  o.amount > 100
AND    c.cust_id = o.cust_id
AND    c.language = 'en'

Forgetting to escape input to the LIKE predicate

Việc dùng kí tự bỏ qua rất quan trọng khi đối chiếu mẫu, để tránh việc hiểu nhầm các kí tự đặc biệt khi ta muốn dùng nó như một giá trị hằng.

Ví dụ như bạn muốn tìm các kí tự a% hay a_ chẳng hạn.

Lưu ý là chỉ có thể định nghĩa kí tự bỏ qua bằng 1 kí tự (độ dài bằng 1).

SELECT *
FROM nhanvien
WHERE secret_hint LIKE ‘123!%455’ ESCAPE ‘!’;

Lệnh này sẽ trả về các nhân viên có secret_hint là 123%455. Kí tự % không còn được dùng với ý nghĩa trước đó mà như một kí tự bình thường.

Đây là 1 ví dụ phức tạp hơn.

SELECT * 
FROM nhanvien
WHERE secret_hint LIKE ‘H%!%’ ESCAPE ‘!’;

Kết quả trả về của điều kiện LIKE là các nhân viên có secret_hint bắt đầu bằng “H” và kết thúc bằng “%”, ví dụ như “Help%”.

Có thể dùng kí tự bỏ qua với cả kí tự _ trong điều kiện LIKE.

SELECT * FROM nhanvien WHERE secret_hint LIKE ‘H%!’ ESCAPE ‘!’; Ví dụ này trả về các nhân viên có secret_hint bắt đầu bằng “H” và kết thúc bằng “”, ví dụ như “Help_”.

Thinking that NOT (A IN (X, Y)) is the boolean inverse of A IN (X, Y)

Điều này là tinh tế nhưng rất quan trọng đối với NULL! Hãy xem lại ý nghĩa của A IN (X, Y):

                  A IN (X, Y)
is the same as    A = ANY (X, Y)
is the same as    A = X OR A = Y

Trong khi đó, NOT (A IN (X, Y)) thực sự có nghĩa là:

                  NOT (A IN (X, Y))
is the same as    A NOT IN (X, Y)
is the same as    A != ANY (X, Y)
is the same as    A != X AND A != Y

Nếu bất kỳ X hoặc Y nào là NULL, thì vị từ NOT IN sẽ dẫn đến UNKNOWN trong khi vị từ IN vẫn có thể trả về giá trị boolean. Hay nói cách khác, khi A IN (X, Y) mang lại TRUE hoặc FALSE, NOT (A IN (X, Y)) vẫn có thể mang lại UNKNOWN thay vì FALSE hoặc TRUE.
Vì vậy cần cẩn thận với vị từ NOT IN khi các cột có thể là nullable.

Thinking that NOT (A IS NULL) is the same as A IS NOT NULL

Nhớ rằng

                   NOT (A IS NULL)
is not the same as A IS NOT NULL

Nếu A là giá trị của một row trả về, thì bảng chân lý được biến đổi sao cho:

- A IS NULL yields true only if all values in A are NULL
- NOT(A IS NULL) yields false only if all values in A are NULL
- A IS NOT NULL yields true only if all values in A are NOT NULL
- NOT(A IS NOT NULL) yields false only if all values in A are NOT NULL

Not using row value expressions where they are supported

Ví dụ 1:

SELECT c.address
FROM   customer c,
WHERE (c.first_name, c.last_name) = (?, ?)

Ví dụ 2:

SELECT c.first_name, c.last_name, a.street
FROM   customer c
JOIN   address a
  ON  (c.id, c.tenant_id) = (a.id, a.tenant_id)

Biểu thức giá trị hàng là một tính năng SQL tuyệt vời. Biểu thức giá trị hàng cho phép bạn mô tả các vị từ phức dễ dàng hơn nhiều. Một ví dụ đơn giản là truy vấn khách hàng về tên và họ cùng một lúc như ví dụ 1 bên trên chẳng hạn.