# WHERE
> WHERE是SELECT敘述句中的一個子句，可以為查詢特定條件來篩選表格中哪些資料應該放在輸出結果中，不符合的則排除

SELECT [回傳資料]
FROM [資料庫結構.表格]
WHERE [篩選條件]
ORDER BY [欲排列的欄位]

* 要注意資料型態

SELECT
    market_date,
    customer_id,
    quantity
    quantity * cost_to_customer_per_qty AS price
FROM farmers_market.customer_purchases
WHERE customer_id = 4
...

如果customer_id欄位值是整數就是4而如果是字串的話就要是'4'

## 多重條件篩選

AND, OR, AND NOT

1. OR是使用『短路求值』- 一確認就不再檢查其他條件
2. 超過兩個條件式時，可以用小括號將個別條件式括起來。 判斷順序是小括號內條件式優先處理

SELECT
    product_id,
    product_name,
FROM farmers_market.product
WHERE
    product_id = 10
    OR (product_id > 3
    AND product_id < 8)

## 數種用於篩選的關鍵字
1. BETWEEN AND

SELECT *
FROM farmers_market.vendor_booth_assignments
WHERE
    vendor_id = 7
    AND market_date BETWEEN '2019-04-03' AND '2019-04-16'
...

2. IN

SELECT
    customer_id,
    customer_first_name,
    customer_last_name
FROM farmers_market.customer
WHERE 
    customer_last_name IN ('Johnny', 'Kakas', 'Victor', 'Ken')
...

3. LIKE
%:代表不限個數的字元

SELECT
    customer_id,
    customer_first_name,
    customer_last_name
FROM farmers_market.customer
WHERE 
    customer_last_name LIKE 'Jer%'
...

_ : 代表一個未知字元
[] : 指定匹配的字符集和 -> [abc]
^ : 指定非匹配的字符集和 -> [^abc]

4. IS NULL

SELECT
FROM farmers_market.product
WHERE 
    product_size IS NULL
...

* 空白跟NULL並不相同
* 因為NULL並非值，所以不能用=做比較

## 透過子查詢做篩選

SELECT
    market_date,
    customer_id,
    vendor_id,
    quantity * cost_to_customer_per_qty AS price
FROM farmers_market.customer_purchases
WHERE
    market_date IN
    (
        SELECT market_date
        FROM farmers_market.market_date_info
        WHERE market_date >= '2019-07-01'
        AND market_date <= '2019-12-31'
        AND market_rain_flag = 1
    )

# CASE

SELECT ... AS
CASE ... AS 

別名欄位：表格中原本存在的欄位所產生的新欄位
衍生欄位：經過轉換創造出來的新欄位

CASE
    WHEN [條件1]
        THEN [指定值或做計算]
    WHEN [條件2]
        THEN [指定值或做計算]
    ELSE [指定值或做計算]
END

* SQL中的別名欄位無法在當下查詢中立即使用 -> 因為SELECT是在FROM WHERE等子句後執行

## CASE進行類別編碼
1. 類別有高低等級之分

SELECT
    booth_number,
    booth_price_level,
    CASE
        WHEN booth_price_level = 'A' THEN 1
        WHEN booth_price_level = 'B' THEN 2
        WHEN booth_price_level = 'A' THEN 3
    END AS booth_price_level_numeric
FROM farmers_market.booth
LIMIT 5

2. 類別都是平等的 -> One-hot編碼

SELECT
    ProductID,
    ProductName,
    CASE WHEN Category = 'Electronics' THEN 1 ELSE 0 END AS IsElectronics,
    CASE WHEN Category = 'Clothing' THEN 1 ELSE 0 END AS IsClothing,
    CASE WHEN Category = 'Furniture' THEN 1 ELSE 0 END AS IsFurniture
FROM Products;

| ProductID | ProductName | IsElectronics | IsClothing | IsFurniture |
|-----------|-------------|---------------|------------|-------------|
| 1 | ProductA | 1 | 0 | 0 |
| 2 | ProductB | 0 | 1 | 0 |
| 3 | ProductC | 1 | 0 | 0 |
| 4 | ProductD | 0 | 0 | 1 |
| 5 | ProductE | 0 | 1 | 0 |