[SQL]WHERE 1=1 與 WHERE 1=0 效用

tags: SQL database

WHERE 1=1

2019 11 06

原文 & 參考：
https://dotblogs.com.tw/invercent914/2013/09/16/118728

增加WHERE 1 = 1不影響輸出結果，但卻可以在SQL語法組合過程中簡化流程，所以偶爾還是可以在程式碼中見到它的身影。

沒有使用 WHERE 1=1 :

string sql = "";
bool andFlag = false;  //宣告一個旗標決定要不要串上AND字串
sql += "SELECT * ";
sql += "FROM BOOK ";

if(有任何篩選條件){
    sql += "WHERE "  ;

    if(有篩選作者){
        sql += "Author = 'author1' ";
        andFlag = true;
    }


    if(有篩選出版社){
        if(andFlag)
            sql += " AND ";
        sql += "Publisher = 'Longman' ";
    }

  
    if(有篩選價格){
        if(andFlag)
            sql += " AND ";
        sql += "Price = 100 ";
    }
}

//接著進行查詢

使用 WHERE 1=1 :

string sql = "";
sql += "SELECT * ";
sql += "FROM BOOK ";
sql += "WHERE 1=1 ";

if(有篩選作者)
    sql += "AND Author = 'author1' ";

if(有篩選出版社)
    sql += "AND Publisher = 'Longman' ";
  
if(有篩選價格)
    sql += "AND Price = 100 ";


//接著進行查詢

WHERE 1=0

只傳回SELECT結果的表格結構

CREATE TABLE tableName AS
SELECT
  *
FROM
  BOOK
WHERE
  1 = 0