値式の詳細は、第2.6節を参照してください。
SQLでは、IS NULL条件がUNKNOWNと評価されることはありません。常にTRUEまたはFALSEになります。値式がNULLの場合、この条件はTRUEと評価されます。値式がNULLでない場合、この条件はFALSEと評価されます。
特定の列にNULL値が含まれている行を取得するには、IS NULL条件を使用します。IS NULL条件は、行の特定の列がNULL値を持つかどうかをテストすることによって結果表に含まれる行が決まる問合せを構成する唯一の方法です。NOT LIKEまたは<>(等しくない)などの他の構成では、NULL値を持つ行は結果表に含まれません。
例: IS NULL条件を使用した、NULL値に基づく行の取得
次の例は、NULL値を持つ行を取得するにはIS NULL条件を使用する必要があることを示しています。
SQL> -- The following query does not include rows that SQL> -- have null values in the MIDDLE_INITIAL column: SQL> -- SQL> SELECT COUNT(*) FROM EMPLOYEES cont> WHERE NOT (MIDDLE_INITIAL = 'V'); 60 1 row selected SQL> -- SQL> -- To get a count of rows that have no values stored in SQL> -- the MIDDLE_INITIAL column, use an IS NULL predicate. SQL> -- SQL> SELECT COUNT(*) FROM EMPLOYEES cont> WHERE MIDDLE_INITIAL IS NULL; 36 1 row selected |
LIKE条件では、文字列リテラルのパターン・マッチ検索が行われます。LIKE条件では大/小文字が区別されます。同じ文字の大/小文字は異なる文字とみなされます。
LIKE条件では大/小文字が区別されるため、DEC Multinational Character Setの大文字で検索した結果に小文字は含まれません。その逆も同様です。たとえば、LIKE "Ç"とLIKE "ç"では、異なるレコードのセットが取得されます。
LIKE条件では、DEC Multinational Character Setで使用される発音区別符号が区別されます。したがって、aはAと一致しますが、á、à、ä、Á、À、Âなどとは一致しません。
スペイン語では、chとllは一意の単一の文字として処理されます。たとえば、ドメインが照合順番SPANISHで定義されている場合、LIKE "c%"ではcharは取得されませんが、catは取得されます。
LIKE条件の形式は、次のとおりです。
SQLでは、value-expr引数は文字列として解釈され、patternと比較されます。patternはテキスト・データ型の値式である必要があります。
pattern内では、パーセント記号(%)、アンダースコア(_)およびエスケープ文字は特別な意味を持ちます。
pattern内の文字 | 一致する文字 |
---|---|
% | 任意の文字列 |
_ | 任意の文字 |
escape-character % | % |
escape-character _ | _ |
escape-character escape-character | エスケープ文字 |
指定可能なのは、パーセント記号、アンダースコアまたはエスケープ文字自体のみです。その他の文字は無効になり、エラーが返されます。
ワイルドカードを使用する必要がない場合、Oracle Rdbでは、LIKE条件ではなく基本条件を使用することをお薦めします。ワイルドカードを使用する必要がある場合は、常にパーセント記号を使用してください。 |
Oracle Rdbでは、IGNORE CASE句が指定されていないLIKE条件が含まれているSQL問合せのパフォーマンスを向上できます。このような問合せの最適化は、LIKE条件の文字列がワイルドカード文字(%または_)またはエスケープ文字を含まない1文字以上のパターンで始まる場合に行われます。
たとえば、次のLIKE条件では、LIKE条件の文字列がRANというパターンで始まっており、ワイルドカード文字もエスケープ文字も含まれず、IGNORE CASE句も指定されていないため、このLIKE条件は最適化されます。
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'RAN%D%'; |
パターンのプリフィックス(RAN)が既知の場合は、このプリフィックスを使用して索引の範囲が特定され、問合せのパフォーマンスが向上します。パターンには、任意の表現を自由に指定できます。また、コンパイル時定数である必要もありません。
これに対して、次の問合せのLIKE条件では、効率的な索引の取得を妨げるワイルドカード文字でパターンが始まっているため、索引範囲の最適化は適用されません。
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE '%RAN'; |
LIKE条件には、次の制限があります。
SQL> SHOW TABLE (COLUMNS) T1; Information for table T1 Columns for table T1: Column Name Data Type Domain ----------- --------- ------ CHR CHAR(10) VARCHR VARCHAR(10) SQL> INSERT INTO T1 cont> (CHR, VARCHR) cont> VALUES ('abc', 'abc'); 1 row inserted SQL> -- SQL> SELECT CHR FROM T1 WHERE CHR LIKE 'abc'; 0 rows selected SQL> -- SQL> SELECT VARCHR FROM T1 WHERE VARCHR LIKE 'abc'; VARCHR abc 1 row selected |
この例では、同じ文字列リテラル値がCHR列およびVARCHR列に挿入されています。ただし、CHARデータ型では文字列リテラルの後ろが7文字分の空白で埋め込まれますが、LIKE条件では空白が埋め込まれないため、LIKE条件では異なる結果が返されます。CHR列の行を選択する場合は、次のSELECTコマンドを発行する必要があります。
SQL> SELECT CHR FROM T1 WHERE CHR LIKE 'abc '; -- abc plus 7 spaces CHR abc 1 row selected |
パターン・マッチ用のホスト変数を宣言する場合は、空白が埋め込まれるのを回避するためにVARCHARデータ型を使用します。
値式の詳細は、第2.6節を参照してください。リテラルの詳細は第2.4節、パラメータの詳細は第2.2.13項を参照してください。
表2-34は、サポートされているキャラクタ・セットのワイルドカード文字を示しています。
キャラクタ・セット | アンダースコア | パーセント |
---|---|---|
DEC_MCS | %X'5F' | %X'25' |
BIG5 | %X'A1C4' | %X'A248' |
AL24UTFFSS | %X'5F' | %X'25' |
ISOLATINARABIC | %X'5F' | %X'25' |
ASCII | %X'5F' | %X'25' |
DOS_LATIN1 | %X'5F' | %X'25' |
DOS_LATINUS | %X'5F' | %X'25' |
GB18030 | %X'5F' | %X'25' |
ISOLATIN1 | %X'5F' | %X'25' |
ISOLATIN9 | %X'5F' | %X'25' |
ISOLATINCYRILLIC | %X'5F' | %X'25' |
ISOLATINGREEK | %X'5F' | %X'25' |
ISOLATINHEBREW | %X'5F' | %X'25' |
DEVANAGARI | %X'5F' | %X'25' |
KATAKANA | %X'5F' | %X'25' |
KANJI | %X'A1B2' | %X'A1F3' |
DEC_KANJI | %X'5F' | %X'25' |
HANZI | %X'A3DF' | %X'A3A5' |
DEC_HANZI | %X'5F' | %X'25' |
KOREAN | %X'A3DF' | %X'A3A5' |
DEC_KOREAN | %X'5F' | %X'25' |
HANYU | %X'A2A8' | %X'A2A5' |
DEC_SICGCC | %X'5F' | %X'25' |
DEC_HANYU | %X'5F' | %X'25' |
SHIFT_JIS | %X'5F' | %X'25' |
TACTIS | %X'5F' | %X'25' |
HEX | %X'3546' | %X'3235' |
UNICODE | %X'005F' | %X'0025' |
UTF8 | %X'5F' | %X'25' |
WIN_ARABIC | %X'5F' | %X'25' |
WIN_GREEK | %X'5F' | %X'25' |
WIN_CYRILLIC | %X'5F' | %X'25' |
WIN_HEBREW | %X'5F' | %X'25' |
WIN_LATIN1 | %X'5F' | %X'25' |
例1: LIKE条件および空白のない引数の使用
SQL> -- Notice that the LAST_NAME column SQL> -- in the EMPLOYEES table has 14 characters: SQL> SHOW TABLE EMPLOYEES Information for table EMPLOYEES Comment on table EMPLOYEES: personal information about each employee Columns for table EMPLOYEES: Column Name Data Type Domain ----------- --------- ------ EMPLOYEE_ID CHAR(5) ID_DOM LAST_NAME CHAR(14) LAST_NAME_DOM . . . SQL> -- That means the following statement will not find the row for SQL> -- Toliver because the LIKE predicate does not pad arguments with SQL> -- blanks, and the character string "Toliver" only has 7 characters. SQL> -- SQL> SELECT LAST_NAME FROM EMPLOYEES cont> WHERE LAST_NAME LIKE 'Toliver'; 0 rows selected SQL> -- SQL> -- To find the row for Toliver using the LIKE predicate, use the SQL> -- percent sign wildcard. Note that you can also explicitly pad SQL> -- the string by typing 7 underscore characters following the SQL> -- word Toliver. SQL> -- SQL> SELECT LAST_NAME FROM EMPLOYEES cont> WHERE LAST_NAME LIKE 'Toliver%'; LAST_NAME Toliver 1 row selected SQL> -- |
例2: LIKE条件およびパーセント記号ワイルドカード文字の使用
1つのパーセント記号ワイルドカードがアンダースコア文字とともに使用されている場合、問合せでは最初の文字の直後がonであるすべての姓が取得されます。この例では、アンダースコアは姓の最初の文字を表し、パーセント記号ワイルドカードはonの後の任意の文字を表しています。
SQL> SELECT DISTINCT LAST_NAME cont> FROM EMPLOYEES cont> WHERE LAST_NAME LIKE '_on%'; LAST_NAME Connolly Lonergan 2 rows selected |
2つのパーセント記号ワイルドカードが使用されている場合、問合せでは名前のいずれかの位置にonが含まれるすべての姓が取得されます。パーセント記号ワイルドカードはonの前後の任意の文字を表してしています。
SQL> SELECT DISTINCT LAST_NAME cont> FROM EMPLOYEES cont> WHERE LAST_NAME LIKE '%on%'; LAST_NAME Burton Canonica Clairmont Clinton Connolly Goldstone . . . |
例3: 数値データ型を指定したLIKE条件の使用
LIKE条件では数値データ型も使用できますが、その値は文字列リテラルと比較されます。数字3で始まる給与を検索します。
SQL> SELECT SALARY_AMOUNT cont> FROM SALARY_HISTORY cont> WHERE SALARY_AMOUNT LIKE '3%'; %SQL-I-NUMCMPTXT, Numeric column will be compared with string literal as text SALARY_AMOUNT 30598.00 30880.00 . . . |
この例は、30,000〜39,999ドルの範囲にあるすべての給与を検索する方法としては使用できません。SALARY_AMOUNT列に値398が格納されている場合、問合せではこの値も取得されます。
例4: LIKE条件におけるマッチ・パターン
onという文字で終わる従業員の姓を検索します。列LAST_NAMEは長さが14文字(CHAR(14))であり、この例のマッチ・パターンではonシーケンスの後に7つの空白が明示的に指定されているため、この問合せではonで終わる7文字の姓のみが取得されます。
SQL> SELECT DISTINCT LAST_NAME cont> FROM EMPLOYEES cont> WHERE LAST_NAME LIKE '%on '; LAST_NAME Clinton Jackson Johnson 3 rows selected |
マッチ・パターン内で明示的に指定する空白の数を増やすと、onで終わるより短い姓が問合せで取得されます。マッチ・パターン内で明示的に指定する空白の数を減らすと、onで終わるより長い姓が問合せで取得されます。
例5: LIKE条件におけるエスケープ文字の使用
SALARY_HISTORYレコードのREMARKS列で昇給する従業員をすべて検索します。
SQL> SELECT LAST_NAME, REMARKS cont> FROM SALARY_HISTORY cont> WHERE REMARKS cont> LIKE '%&% increase%' ESCAPE '&'; LAST_NAME REMARKS Clinton 10% increase Jackson 10% increase Johnson 10% increase 3 rows selected |
LIKE条件では、ワイルドカード文字のパーセント記号(%)が含まれている文字列を検索する必要があります。パーセント記号自体を検索するには、検索文字列内のパーセント記号を示すエスケープ文字としてアンパサンド(&)を問合せで指定します。
例6: LIKE条件における大/小文字を区別するマッチ・パターン
boyという文字が含まれている従業員の姓を検索します。LIKE条件では大/小文字が区別され、LAST_NAME列は大/小文字を使用して入力されているため、%Boy%に一致する行が検索されます。ただし、%BOY%に一致する行は検索されません。
SQL> SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME cont> FROM EMPLOYEES E cont> WHERE LAST_NAME LIKE '%Boy%'; EMPLOYEE_ID LAST_NAME FIRST_NAME 00244 Boyd Ann 00226 Boyd James 2 rows selected SQL> -- SQL> SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME cont> FROM EMPLOYEES E cont> WHERE LAST_NAME LIKE '%BOY%'; 0 rows selected SQL> --You can also use the IGNORE CASE clause to get a case-insensitive match. SQL> --Note that the % wildcard is used to search for padded SQL> --characters that might be stored in the LAST_NAME column. SQL> -- SQL> SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME cont> FROM EMPLOYEES cont> WHERE LAST_NAME LIKE '%BOY%' IGNORE CASE; EMPLOYEE_ID LAST_NAME FIRST_NAME 00244 Boyd Ann 00226 Boyd James 2 rows selected |
次の例は、列参照との一致を使用したLIKE条件の使用方法を示しています。
SQL> CREATE TABLE PATTERN cont> (A CHAR VARYING(10)); SQL> -- SQL> INSERT INTO PATTERN cont> (A) cont> VALUES cont> ('T%'); SQL> -- SQL> SELECT LAST_NAME FROM EMPLOYEES, PATTERN cont> WHERE LAST_NAME LIKE cont> (SELECT A FROM PATTERN); EMPLOYEES.LAST_NAME Tarbassian Toliver 2 rows selected SQL> |
限定条件では、値と値の集合が比較されます。限定条件では、2番目のオペランドにALL、ANYまたはSOMEの比較演算子が前に付く列選択式を指定する必要があるという点を除き、基本条件と同じ形式です。
値式の詳細は、第2.6節を参照してください。列選択式の詳細は、第2.8.2項を参照してください。
表2-35は、限定条件における値の比較に基づいた結果の値を示しています。
値の比較 | 結果 |
---|---|
ALL限定条件 | |
すべての比較がTRUEの場合 | TRUE |
比較がFALSEの場合 | FALSE |
比較が行われない場合 | TRUE |
それ以外 | UNKNOWN |
SOME限定条件およびANY限定条件 | |
比較がTRUEの場合 | TRUE |
すべての比較がFALSEの場合 | FALSE |
比較が行われない場合 | TRUE |
それ以外 | UNKNOWN |
例: 限定条件の使用
例1: ALL演算子を指定した限定条件の使用
次の例は、限定条件でALL演算子を使用して、最年長および最年少の従業員を検索しています。
SQL> SELECT FIRST_NAME, LAST_NAME, BIRTHDAY FROM EMPLOYEES cont> WHERE cont> BIRTHDAY <= ALL (SELECT BIRTHDAY FROM EMPLOYEES) cont> OR cont> BIRTHDAY >= ALL (SELECT BIRTHDAY FROM EMPLOYEES); FIRST_NAME LAST_NAME BIRTHDAY Rick O'Sullivan 12-JAN-1923 James Stornelli 10-JAN-1960 2 rows selected |
次の例は、限定条件でANY演算子を使用して、大学の学位を持つ従業員名を検索しています。この問合せは、「DEGREES表に対応する行が少なくとも1行はある、すべての従業員の姓およびID番号を選択する」と表現できます。
SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID cont> FROM EMPLOYEES E cont> WHERE E.EMPLOYEE_ID cont> = ANY -- same as = SOME cont> (SELECT D.EMPLOYEE_ID cont> FROM DEGREES D); |
この例から、大学の学位を持たない従業員名を返す、<> ANY限定条件を使用した類似の問合せが考えられます。次の例は、EMPLOYEES表にあるそのようなすべての行を取得する問合せを示しています。
SQL> SQL> -- Notice that the <> ANY predicate does *not* find SQL> -- the names of employees without degrees. Instead, it retrieves SQL> -- the names of employees from the EMPLOYEES table whose employee SQL> -- IDs are not equal to any *one* of the EMPLOYEE_ID values in SQL> -- the DEGREES table (in other words, every row in the EMPLOYEES table). SQL> -- SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID cont> FROM EMPLOYEES E cont> WHERE E.EMPLOYEE_ID <> ANY cont> (SELECT D.EMPLOYEE_ID cont> FROM DEGREES D); LAST_NAME EMPLOYEE_ID Toliver 00164 Smith 00165 . . . . . . SQL> SQL> -- To retrieve the names of employees without degrees, use the SQL> -- NOT Boolean operator to negate the = ANY quantified predicate. SQL> -- (Another way to retrieve this information is with the SQL> -- NOT EXISTS predicate.) SQL> SELECT LAST_NAME, EMPLOYEE_ID cont> FROM EMPLOYEES cont> WHERE NOT (EMPLOYEE_ID = ANY cont> (SELECT EMPLOYEE_ID FROM DEGREES)); LAST_NAME EMPLOYEE_ID Goldstone 00178 1 row selected |