# SQL比較運算子

2019-10-16 22:50:50

1 `=` 等於
2 `<>` 不等於
3 `>` 大於
4 `>=` 大於或等於
5 `<` 小於
6 `<=` 小於或等於
7 `!=` 不等於

#### 1. 等於(=)運算子

``````expression1 = expression2
``````

``````SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
last_name = 'Lee';
``````

``````+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         100 | Steven     | Lee       |
|         103 | Alexander  | Lee       |
+-------------+------------+-----------+
2 rows in set
``````

``````SELECT
employee_id, first_name, last_name, phone_number
FROM
employees
WHERE
phone_number = NULL;
``````

``````phone_number = NULL
``````

``````SELECT
employee_id, first_name, last_name, phone_number
FROM
employees
WHERE
phone_number IS NULL;
``````

``````+-------------+------------+-----------+--------------+
| employee_id | first_name | last_name | phone_number |
+-------------+------------+-----------+--------------+
|         145 | John       | Liu       | NULL         |
|         146 | Karen      | Liu       | NULL         |
|         176 | Jonathon   | Yang      | NULL         |
|         177 | Jack       | Yang      | NULL         |
|         178 | Kimberely  | Yang      | NULL         |
|         179 | Charles    | Yang      | NULL         |
+-------------+------------+-----------+--------------+
6 rows in set
``````

#### 2. 不等於(<>)運算子

``````expression1 <> expression2
``````

``````SELECT
employee_id, first_name, last_name, department_id
FROM
employees
WHERE
department_id <> 8
ORDER BY first_name , last_name;
``````

``````+-------------+------------+-----------+---------------+
| employee_id | first_name | last_name | department_id |
+-------------+------------+-----------+---------------+
|         103 | Alexander  | Lee       |             6 |
|         115 | Alexander  | Su        |             3 |
|         114 | Avg        | Su        |             3 |
|         193 | Britney    | Zhao      |             5 |
|         104 | Bruce      | Wong      |             6 |
|         109 | Daniel     | Chen      |            10 |
... ...
|         100 | Steven     | Lee       |             9 |
|         203 | Susan      | Zhou      |             4 |
|         106 | Valli      | Chen      |             6 |
|         206 | William    | Wu        |            11 |
+-------------+------------+-----------+---------------+
34 rows in set
``````

``````SELECT
employee_id, first_name, last_name, department_id
FROM
employees
WHERE
department_id <> 8
AND department_id <> 10
ORDER BY first_name , last_name;
``````

``````+-------------+------------+-----------+---------------+
| employee_id | first_name | last_name | department_id |
+-------------+------------+-----------+---------------+
|         103 | Alexander  | Lee       |             6 |
|         115 | Alexander  | Su        |             3 |
|         114 | Avg        | Su        |             3 |
|         193 | Britney    | Zhao      |             5 |
|         104 | Bruce      | Wong      |             6 |
|         105 | David      | Liang     |             6 |
|         107 | Diana      | Chen      |             6 |
|         118 | Guy        | Zhang     |             3 |
... ...
|         117 | Sigal      | Zhang     |             3 |
|         100 | Steven     | Lee       |             9 |
|         203 | Susan      | Zhou      |             4 |
|         106 | Valli      | Chen      |             6 |
|         206 | William    | Wu        |            11 |
+-------------+------------+-----------+---------------+
28 rows in set
``````

#### 3. 大於(>)運算子

``````expression1 > expression2
``````

``````SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > 12000
ORDER BY salary DESC;
``````

``````+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         100 | Steven     | Lee       | 24000  |
|         101 | Neena      | Wong      | 17000  |
|         102 | Lex        | Liang     | 17000  |
|         145 | John       | Liu       | 14000  |
|         146 | Karen      | Liu       | 13500  |
|         201 | Michael    | Zhou      | 13000  |
+-------------+------------+-----------+--------+
``````

``````SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > 12000 AND department_id = 8
ORDER BY salary DESC;
``````

``````+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         145 | John       | Liu       | 14000  |
|         146 | Karen      | Liu       | 13500  |
+-------------+------------+-----------+--------+
2 rows in set
``````

#### 4. 大於等於(>=)運算子

``````expression1 >= expression2
``````

``````SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary >= 9999
ORDER BY salary;
``````

``````+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         204 | Hermann    | Wu        | 10000  |
|         114 | Avg        | Su        | 11000  |
|         108 | Nancy      | Chen      | 12000  |
|         205 | Shelley    | Wu        | 12000  |
|         201 | Michael    | Zhou      | 13000  |
|         146 | Karen      | Liu       | 13500  |
|         145 | John       | Liu       | 14000  |
|         101 | Neena      | Wong      | 17000  |
|         102 | Lex        | Liang     | 17000  |
|         100 | Steven     | Lee       | 24000  |
+-------------+------------+-----------+--------+
10 rows in set
``````

#### 5. 小於或等於(<=)運算子

``````expression1 <= expression2
``````

``````SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary <= 3500
ORDER BY salary;
``````

``````+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
|         119 | Karen      | Zhang     | 2500   |
|         118 | Guy        | Zhang     | 2600   |
|         126 | Irene      | Liu       | 2700   |
|         117 | Sigal      | Zhang     | 2800   |
|         116 | Shelli     | Zhang     | 2900   |
|         115 | Alexander  | Su        | 3100   |
+-------------+------------+-----------+--------+
6 rows in set
``````