SQL 設計小技巧--用 ISNULL 或 NVL 達到選擇性條件的下法

透過表單介面查詢資料庫時，常會遇到一種情況，即表單介面上有很多查詢選項，但不一定都要設定。若是不設定，代表 "忽略" 那條查詢選項。

舉個例子，假設畫面上有個欄位是要依照 "學歷" 及 "性別" 取出會員資料，因此，查詢資料庫的 SQL 可能如下：

   select * from member where education = @education and gender = @gender

若此時使用者未設定學歷條件，則查詢語法變成：

   select * from member where education = @education

當條件變動，還有另外兩種可能的查詢語法：

   select * from member where gender = @gender

   select * from member where education

如果你是用 PHP，由前端先組好查詢法語，再傳入後端資料庫處理，這沒有多大問題，只要透過一些 if 的判斷即可。但想像一下，若有 10 個查詢選項，那組合 SQL 語法的過程，不會讓你感到高興吧。

如果你是透過 ORMapping，雖然可以避免自行組合 SQL 查詢語法，卻也免不了要判斷該參數是否已設定，有設定的參數，才需加入 query criteria 中。

透過程式語言組裝查詢條件，再送入資料庫查詢，除了設計上的不便外，也不是所有的情況下都可使用。例如，假設你使用微軟的 Reporting Services，報表資料集的來源，直接就對應到 "靜態的" SQL 語法，你無法在執行期才去組裝它，這時就要使用不同的作法。

一個好用的技巧，是善用 ISNULL (MS SQL/T-SQL) 或 NVL (Oracle/PLSQL) 函式，它們的語法近似：

• ISNULL(A, B)
• NVL(A, B)

上面兩個式子，都代表著，若是 A 不等於 NULL，則傳回 A，否則傳回 B。

這個東西的有用之處，讓我們直接透過例子來解釋，把上面的查詢語句改為如下：

   --MS SQL
   select * from member where education = ISNULL(@education, education) and gender = ISNULL(@gender, gander)

   --Oracle
   select * from member where education = NVL(:education, education) and gender = NVL(:gender, gander)

類似這樣的式子，就可以解決我們所有的查詢組合情況。我們取出其中一段做分解說明：

   gender = ISNULL(@gender, gander)

@gender 代表查詢參數，當 @gender 未設定查詢條件時，我們讓他預設值為 NULL，結果 gender = ISNULL(NULL, gander) 就變成 gender = gender，而這個條件總是成立，所以就等於沒下這個條件。反之，或 gender 設定為 'F' 時，gender = ISNULL('F', gander) 就變成 gender = 'F'，如此一來，就只會查詢出性別為 F 的會員資料。

基於這一原理，即使你的查詢條件有 20 個、30 個，你也不用再費心思索如何組裝查詢條件了。