---
# System prepended metadata

title: WHERE

---

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

```sql=
SELECT [回傳資料]
FROM [資料庫結構.表格]
WHERE [篩選條件]
ORDER BY [欲排列的欄位]
```
* 要注意資料型態
```sql=
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'

## 多重條件篩選
```sql=
AND, OR, AND NOT
```

1. OR是使用『短路求值』- 一確認就不再檢查其他條件
2. 超過兩個條件式時，可以用小括號將個別條件式括起來。 判斷順序是小括號內條件式優先處理
```sql=
SELECT
    product_id,
    product_name,
FROM farmers_market.product
WHERE
    product_id = 10
    OR (product_id > 3
    AND product_id < 8)
```

## 數種用於篩選的關鍵字
1. BETWEEN AND
```sql=
SELECT *
FROM farmers_market.vendor_booth_assignments
WHERE
    vendor_id = 7
    AND market_date BETWEEN '2019-04-03' AND '2019-04-16'
...
```
2. IN
```sql=
SELECT
    customer_id,
    customer_first_name,
    customer_last_name
FROM farmers_market.customer
WHERE 
    customer_last_name IN ('Johnny', 'Kakas', 'Victor', 'Ken')
...
```
3. LIKE
%:代表不限個數的字元
```sql=
SELECT
    customer_id,
    customer_first_name,
    customer_last_name
FROM farmers_market.customer
WHERE 
    customer_last_name LIKE 'Jer%'
...
```
_ : 代表一個未知字元
[] : 指定匹配的字符集和 -> [abc]
^ : 指定非匹配的字符集和 -> [^abc]

4. IS NULL
```sql=
SELECT
FROM farmers_market.product
WHERE 
    product_size IS NULL
...
```
* 空白跟NULL並不相同
* 因為NULL並非值，所以不能用=做比較

## 透過子查詢做篩選
```sql=
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
```sql=
SELECT ... AS
CASE ... AS 
```
別名欄位：表格中原本存在的欄位所產生的新欄位
衍生欄位：經過轉換創造出來的新欄位

```sql=
CASE
    WHEN [條件1]
        THEN [指定值或做計算]
    WHEN [條件2]
        THEN [指定值或做計算]
    ELSE [指定值或做計算]
END
```
* SQL中的別名欄位無法在當下查詢中立即使用 -> 因為SELECT是在FROM WHERE等子句後執行

## CASE進行類別編碼
1. 類別有高低等級之分
```sql=
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編碼
```sql=
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           |
