SELECT fname, lname
FROM employees
WHERE lname = 'Smith'
<> !=
不等於
<> 'Active'
SELECT fname, lname
FROM employees
WHERE status <> 'Active'
>
大於
> '01 Jan 1995'**
SELECT fname, lname
FROM employees
WHERE hire_date >
'12/31/90'
<
小於
< 100
SELECT fname, lname
FROM employees
WHERE job_lvl < 100
>= !<
大於或等於
>= 'T'
SELECT au_lname
FROM authors
WHERE au_lname >= 'T'
<= !>
小於或等於
<= '01 Jan 1995'**
SELECT fname, lname
FROM employees
WHERE hire_date <=
'01/01/95'
BETWEEN expr1 AND expr2
測試值的範圍
BETWEEN
'01 Jan 1995'
AND
'31 Dec 1995'**
SELECT fname, lname
FROM employees
WHERE hire_date
BETWEEN '12/31/90'
AND '12/31/91'
IS [NOT] NULL
測試資料行的內容或運算式的結果是否為 Null
IS NULL
SELECT fname, lname
FROM employees
WHERE photo_on_file IS NULL
[NOT] LIKE
執行模式比對 (通常限於字元資料型別)
LIKE ('MAC%')
SELECT fname, lname
FROM employees
WHERE lname LIKE ('MAC%')
expr1 [NOT] IN (val1、val2, ...) -或-expr1 [NOT] IN (子查詢)
藉由測試 expr1 是否出現在值清單或子查詢結果集中，比對特定值的清單。
IN ('SW', 'SE')
supplier_id IN
(subquery)
SELECT fname, lname
FROM employees
WHERE sales_region IN ('SW', 'SE')
SELECT product_name
FROM products
WHERE supplier_id IN
(SELECT supplier_id
FROM supplier
WHERE (country = 'Sweden'))
ANY (SOME)
測試子查詢結果集中的一或多個資料列是否符合指定的條件 (ANY 和 SOME 是同義字，[查詢和檢視表設計工具] 會在建立 SQL 陳述式時使用 ANY)。
<> ANY (subquery)
SELECT au_lname, au_fname
FROM authors
where city <> any
(SELECT city FROM publishers)
ALL
測試子查詢結果集中所有的資料列是否符合指定的條件
advance > ALL (subquery)
SELECT title FROM titles
where advance > all
(SELECT advance FROM
publishers,titles
where titles.pub_id
= publishers.pub_id
AND pub_name =
'Alogdata Infosystems')
[NOT] EXISTS
測試子查詢是否都會傳回任何結果 (非特定結果)
EXISTS (subquery)
SELECT product_name
FROM products
WHERE EXISTS
(SELECT * FROM
orders, products
WHERE orders.prod_id
= products.prod_id)
.gif "注意事項")
注意事項