SQL やデータベース操作に慣れている方にはお馴染みのクエリーオブクエリー機能もいくつか含まれています。
ドット表記法の使用
クエリーオブクエリーでは、テーブル名のドット表記法がサポートされています。
例
A という構造体に B というフィールドが含まれており、その中に Products というテーブルが含まれている場合は、次のようなドット表記法でこのテーブルを参照できます。
FROM A.B.Products; |
結合の使用
結合操作では、1 つの SELECT ステートメントを使用して、複数の関連するテーブルから 1 つの結果セットを返します。通常、それらのテーブルは、プライマリキーと外部キーの関係で関連付けられています。結合を実行する SQL 節は 2 つあります。
- WHERE 節：クエリーオブクエリーでは、WHERE 節による結合がサポートされています。
- INNER JOIN および OUTER JOIN: クエリーオブクエリーでは、INNER JOIN または OUTER JOIN 節による結合はサポートされていません。
注意：クエリーオブクエリーでサポートされているのは、2 つのテーブルの結合のみです。 |
UNION の使用
UNION 演算子を使用して、複数の SELECT 式の結果を 1 つのレコードセットに結合できます。結合するテーブルの列数が同じであり、対応する列のデータ型が UNION と互換性がある必要があります。次のいずれかの条件に該当する場合、列のデータ型は UNION と互換性があります。
- 同じデータ型である場合。たとえば両方とも Tinyint である場合。
- 両方ともが数値である場合。たとえば、Tinyint、Smallint、Integer、Bigint、Double、Float、Real、Decimal、Numeric など。
- 両方とも文字である場合。たとえば、Char、Varchar、LongVarchar など。
- 両方とも日付である場合。たとえば、Time、TimeStamp、Date など。
注意：クエリーオブクエリーでは、ODBC 形式の日付および時刻はサポートされていません。 |
シンタックス
select_expression = select_expression UNION [ALL] select_expression |
例
次のテーブルがあるとします。
Table1 |
|
|---|---|
Type(int) |
Name(varchar) |
1 |
テニス |
2 |
野球 |
3 |
サッカー |
Table2 |
|
|---|---|
ID(int) |
Sport(varchar) |
3 |
サッカー |
4 |
バレーボール |
5 |
卓球 |
Table1 と Table2 を結合するには、次のように UNION ステートメントを使用します。
UNION |
この UNION ステートメントによって、次の結果 (UNION) テーブルが作成されます。
結果テーブル |
|
|---|---|
Type(int) |
Name(varchar) |
1 |
テニス |
2 |
野球 |
3 |
サッカー |
4 |
バレーボール |
5 |
卓球 |
列名のエイリアスの使用
UNION テーブルでの列名は、UNION 操作の最初の SELECT ステートメントの結果セットの列名になり、もう一方の SELECT ステートメントの列名は無視されます。結果テーブルの列名を変更するには、次のように エイリアス を使用します。
UNION |
行の重複と複数のテーブル
デフォルトでは、UNION 演算子によって結果テーブルから重複している行が削除されます。キーワード ALL を使用すると、重複している行が含まれるようになります。
次の例のように、UNION 演算子を使用すると無制限にテーブルを結合できます。
UNION |
括弧と評価の順序
デフォルトでは、クエリーオブクエリーの SQL エンジンは、UNION 演算子を含んでいるステートメントを左から右へ評価します。評価順序を変更するには、括弧を使用します。たとえば、次の 2 つのステートメントは異なります。
/* First statement. */ |
ステートメント 1 では、TableB と TableC の結合で重複が除外されます。次に、このセットと TableA の結合で、キーワード ALL によって重複が保持されます。ステートメント 2 では、TableA と TableB の結合で重複が保持されますが、後続の TableC との結合で重複が除外されます。結果として、キーワード ALL の効果はなくなっています。
UNION での他のキーワードの使用
UNION を実行する場合、個々の SELECT ステートメントで ORDER BY 節または COMPUTE 節を指定することはできません。最後の SELECT ステートメントの後ろに ORDER BY または COMPUTE 節を 1 つだけ入れることができます。この節は、結合された最後の結果セットに適用されます。それぞれの SELECT ステートメントには、GROUP BY および HAVING の式のみを指定できます。
条件演算子の使用
クエリーオブクエリーでは、SQL ステートメントで次の条件演算子を使用できます。
テスト条件演算子
この条件演算子は、ブール式が True、False、または Unknown のいずれであるかをテストします。
シンタックス
cond_test ::= expression [IS [NOT] {TRUE | FALSE | UNKNOWN} ] |
例
WHERE _isValid IS true; |
null 条件演算子
この条件演算子は、式が null かどうかをテストします。
シンタックス
null_cond ::= expression IS [NOT] NULL |
例
WHERE bloodVal IS NOT null; |
比較条件演算子
この条件演算子は、ある式と、それと同じデータ型 (数値、文字列、日付、またはブール値) を持つ別の式を比較します。レコードセットの必要な行のみを選択的に取得する場合に使用できます。
シンタックス
comparison_cond ::= expression [= | > | >= | <> | != | < | <=] expression |
例
次の例では、比較条件演算子を使用して、IQ が 150 以上の犬のみを取得します。
FROM Dogs |
BETWEEN 条件演算子
この条件演算子は、式と式を比較します。レコードセットの必要な行のみを選択的に取得する場合に使用できます。比較条件演算子と同様に、BETWEEN 条件演算子も比較を行います。ただし、BETWEEN 条件演算子は、特定の範囲の値との比較を行います。したがって、そのシンタックスには、範囲を表す最小値と最大値の 2 つの値が必要です (それらの値も範囲に含まれます)。これらの値はキーワード AND で区切ります。
シンタックス
between_cond ::= expression [NOT] BETWEEN expression AND expression |
例
次の例では、BETWEEN 条件演算子を使用して、IQ が 150 以上 165 以下の犬のみを取得します。
FROM Dogs |
IN 条件演算子
この条件演算子を使用すると、一致条件のカンマ区切りのリストを指定できます。これは、OR 条件演算子と似ています。IN 条件演算子は、長いリストを使用する場合にコードが読みやすくなるだけでなく、別の SELECT ステートメントを含めることもできます。
シンタックス
in_cond ::= expression [NOT] IN (expression_list) |
例
次の例では、IN 条件演算子を使用して、Ken's Kennels または Barb's Breeders のどちらかで誕生した犬のみを取得します。
FROM Dogs |
LIKE 条件演算子
この条件演算子を使用すると、検索パターンとデータを比較する、ワイルドカード検索を実行できます。LIKE 条件演算子は、データを部分的に不明な値と比較する点で、BETWEEN、IN などの他の条件演算子とは異なります。
シンタックス
like_cond ::= left_string_exp [NOT] LIKE right_string_exp [ESCAPE escape_char] |
left_string_exp には、定数 文字列、 または文字列の列への参照を指定できます。right_string_exp には、文字列の列への参照、または検索パターンを指定できます。検索パターンは、リテラルテキストと 少なくとも 1 つのワイルドカード文字で構成される検索条件です。ワイルドカード文字は、検索パターンの不明な部分を表す特殊文字で、次のように解釈されます。
- アンダースコア (_) は任意の 1 文字を表します。
- パーセント記号 (%) は、0 個以上の文字を表します。
- 角括弧 ([ ]) はその範囲内の任意の文字を表します。
- キャレット付きの角括弧クエリーオブクエリーのユーザーガイドはその範囲外の任意の 文字 を表します。
他のすべての文字は、その文字自体を表します。
注意：以前のバージョンの ColdFusion では、括弧で囲まれた範囲はサポートされていません。
例
次の例では、LIKE 条件演算子を使用して、Boston Terrier、Jack Russell Terrier、Scottish Terrier などの、テリア種の犬のみを取得します。
FROM Dogs |
次の例は、括弧で囲まれた範囲を使用する SELECT ステートメントです。
SELECT lname FROM Suspects WHERE lname LIKE '[a-m]%'; |
大文字と小文字の区別
ColdFusion の通常のルールとは異なり、クエリーオブクエリーでは大文字と小文字が区別されます。ただし、クエリーオブクエリーでは 2 つの文字列関数 UPPER() と LOWER() がサポートされているので、これらを使用すれば大文字と小文字を区別せずに検索を行えます。
例
次の例では、'Sylvester' のみが検索されます。
FROM Dogs |
次の例では、大文字と小文字が区別されません。LOWER() 関数を使用して、「Sylvester」、「 sylvester 」、「SYLVESTER」などをすべて小文字として処理し、すべて小文字の文字列の sylvester でこれらに一致します。
FROM Dogs |
LIKE 条件の右側に変数を使用している場合に、大文字と小文字を区別せずに比較するには、LCase 関数または UCase 関数を使用して、変数のすべてのテキストを大文字または小文字に揃えます。次に例を示します。
WHERE LOWER(dog_name) LIKE '#LCase(FORM.SearchString)#'; |
ワイルドカードのエスケープ
条件演算子の ESCAPE 節を使用して、独自のエスケープ文字を指定できます。
例
次の例では、ESCAPE 節を使用して、通常ワイルドカード文字として解釈されるパーセント記号 (%) を文字として検索できるようにしています。
FROM Benefits |
列のデータ型の管理
クエリーオブクエリーでは、列のデータ型を定義するメタデータが各列に設定されている必要があります。ColdFusion で作成されるすべてのクエリーにはメタデータが設定されます。ただし、QueryNew 関数で 2 番目のパラメーターを省略した場合、作成されるクエリーにはメタデータが含まれません。このオプションの 2 番目のパラメーターを使用して、クエリーの各列のデータ型を定義します。
QueryNew 関数での列のデータ型の指定
QueryNew 関数を入力します。次の例に示すとおり、最初のパラメーターには列名を、2 番目のパラメーターにはデータ型を指定します。
<cfset qInstruments = queryNew("name, instrument, years_playing", "CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_INTEGER")>
注意：クエリーオブクエリーのメタデータを確認するには、GetMetaData 関数を使用します。 |
QueryAddColumn 関数での列のデータ型の指定
パラメータを設定していない QueryNew 関数を指定して、クエリーを作成します。
<cfset myQuery = QueryNew("")>
QueryAddColumn 関数を使用して列を追加します。3 番目のパラメーターでデータ型を指定します。
<cfset FastFoodArray = ArrayNew(1)>
<cfset FastFoodArray[1] = "French Fries">
<cfset FastFoodArray[2] = "Hot Dogs">
<cfset FastFoodArray[3] = "Fried Clams">
<cfset FastFoodArray[4] = "Thick Shakes">
<!--- Use the array to add a column to the query. --->
<cfset nColumnNumber = QueryAddColumn(myQuery, "FastFood", "CF_SQL_VARCHAR",
FastFoodArray)>
データ型を指定しなかった場合は、条件式の実行時に各列の最初の 50 行がチェックされて、データ型が推定されます。
場合によっては、推定されたデータ型がアプリケーションで適切に機能しないことがあります。特に、WHERE 節や他の条件式でその列を使用している場合は、データ型が互換性を持っている必要があります。互換性がない場合は、CAST 関数を使用して、互換性のあるデータ型にいずれかの列を再キャストします。キャストの詳細については、「CAST 関数の使用」を参照してください。データ型の互換性について詳しくは、「クエリーオブクエリーの処理についてクエリーオブクエリーの処理についてクエリーオブクエリーの処理について」を参照してください。
注意：QueryNew 関数でデータ型を指定すると、互換性に関する問題を回避できます。 |
CAST 関数の使用
場合によっては、列のデータ型が、実行したい処理に対して互換性のあるデータ型でないことがあります。例えば、 cfhttp タグによって返されるクエリー列は、コンテンツが数値の場合でも、すべて CF_SQL_VARCHAR 型 です。この場合は、クエリーオブクエリーの CAST 関数を使用して、列の値を適切なデータ型に変換します。
CAST 関数のシンタックスは次のとおりです。
CAST ( expression AS castType ) |
ここで、castType には、次のいずれかが入ります。
- BINARY
- BIGINIT
- BIT
- DATE
- DECIMAL
- DOUBLE
- INTEGER
- TIME
- TIMESTAMP
- VARCHAR
例えば、次のようになります。
<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv" |
集計関数の使用
集計関数は、データのセットを処理して 1 つの値を返します。この関数は、テーブル全体を取得してからテーブル全体のレコードセットを処理するのとは対照的に、テーブルから要約情報を取得します。
次の操作を実行する場合は、集計関数の使用を検討してください。
- 列の平均を表示する
- 列の行数を数える
- 列内で最も早い日付を検出する
すべてのリレーショナルデータベース管理システム（RDBMS）ですべての集計関数がサポートされているわけではありません。データベースのマニュアルを参照してください。次の表に、クエリーオブクエリーでサポートされている集計関数をリストします。
関数 |
説明 |
|---|---|
AVG() |
列の平均を返します。 |
COUNT() |
列の行数を返します。 |
MAX() |
列の最大値を返します。 |
MIN() |
列の最小値 を 返します。 |
SUM() |
列の合計値を返します。 |
シンタックス
([ALL | DISTINCT] numeric_exp) |
例
次の例では、AVG() 関数を使用してすべてのテリアの IQ の平均を取得します。
FROM Dogs |
任意の式への集計関数の適用
クエリーオブクエリーでは次のように、任意の式に対して集計関数を適用できます。
FROM roysched |
集計関数を含む式
クエリーオブクエリーでは次のように、集計関数を含む数式がサポートされています。
FROM roysched |
GROUP BY と HAVING 式の使用
クエリーオブクエリーでは、エイリアスを使用して参照すれば、任意の数式を使用できます。
例
次のコードは、正しいコードです。
SELECT ( lorange + hirange )/2 AS midrange, |
次のコードは、正しいコードです。
SELECT (lorange+hirange)/2 AS x, |
次のコードは、クエリーオブクエリーではサポートされません。
SELECT ( lorange + hirange )/2 AS midrange, |
ORDER BY 節の使用
クエリーオブクエリーでは、ORDER BY 節を使用したソートがサポートされています。これは、SELECT ステートメントの最後に配置する必要があります。複数の列、位置で指定した列、非選択の列を基準にしてソートできます。キーワード DESC を使用すると、降順のソートを指定できます (デフォルトでは、多くの RDBMS ソートは昇順なので、キーワード ASC は不要です)。
シンタックス
order_by_column ::= ( <IDENTIFIER> | <INTEGER_LITERAL> ) [<ASC> | <DESC>] |
例
次の例は、ORDER BY 節を使用した簡単なソートです。
FROM results |
次の例では、より複雑なソートを行っています。まずドーパミンレベルで昇順にソートしてから、アセチルコリンで降順にソートしています。ASC キーワードは 不要です。 読みやすさのためにのみ使用されます。
FROM results |
エイリアスの使用
クエリーオブクエリーでは、データベース列のエイリアスが使用できます。エイリアスとは、データベースフィールドまたは値に割り当てる別名です。エイリアスは、同じ SQL ステートメント内で再利用できます。
エイリアスの使用例としては、複数の列を結合して 1 つの値を生成する場合があります。例えば、 氏 と名を連結して フルネーム の値を作成できます。新しい値はデータベース内に存在しないので、エイリアスを割り当てて参照します。SELECT ステートメントでエイリアスを割り当てるには、AS キーワードを使用します。
例
ORDER BY 節、GROUP BY 節、および HAVING 節でエイリアスを使用できます。
注意：テーブル名にエイリアスを割り当てることはできません。 |
from Employee; |
以降の例は、次の 2 つのマスタークエリーに基づいています。
SELECT * FROM employee |
ORDER BY の例
SELECT (job_id || job_lvl)/2 AS job_value |
GROUP BY の例
SELECT lorange || hirange AS x, count( hirange ) |
HAVING の例
SELECT ( lorange || hirange )/2 AS x, |
null 値の処理
条件式はブール論理 (2 値論理) を使用して処理されますが、null 値を正しく処理するには 3 値論理を使用する必要があります。IS NOT NULL 節は、クエリーオブクエリーで正しく機能します。ただし、次の式は、breed 列が null の場合には正しく機能しません。
WHERE NOT (breed > 'A') |
正しく動作するには、いずれの式の結果セットにも null の breed 列が含まれていないことが必要です。この制限を避けるには、条件に明示的なルールを追加して、次のように変更します。
WHERE breed IS NOT NULL AND not (breed > 'A') |
文字列の連結
クエリーオブクエリーでは、次の例のように、+ および || の 2 つの文字列連結演算子をサポートしています。
LASTNAME || ', ' || FIRSTNAME |
予約語のエスケープ
ColdFusion には予約語があります。その多くは SQL 言語のキーワードであり、通常は列名やテーブル名として使用しません。列名やテーブル名で予約語をエスケープするには括弧で囲みます。
注意：以前のバージョンの ColdFusion では、エスケープしなくても一部の予約語が使用できます。 |
例
次に示す SELECT ステートメントの例は有効です。
SELECT [group]. firstname FROM [group]; |
次の例のようなネストされているエスケープはサポートされていません。
SELECT [[from]] FROM T; |
次の表に、ColdFusion の予約語のリストを示します。
ABSOLUTE |
ACTION |
ADD |
ALL |
ALLOCATE |
ALTER |
AND |
ANY |
ARE |
AS |
ASC |
ASSERTION |
AT |
AUTHORIZATION |
AVG |
BEGIN |
BETWEEN |
BIT |
BIT_LENGTH |
BOTH |
BY |
CASCADE |
CASCADED |
CASE |
CAST |
CATALOG |
CHAR |
CHARACTER |
CHARACTER_LENGTH |
CHAR_LENGTH |
CHECK |
CLOSE |
COALESCE |
COLLATE |
COLLATION |
COLUMN |
COMMIT |
CONNECT |
CONNECTION |
CONSTRAINT |
CONSTRAINTS |
CONTINUE |
CONVERT |
CORRESPONDING |
COUNT |
CREATE |
CROSS |
CURRENT |
CURRENT_DATE |
CURRENT_TIME |
CURRENT_TIMESTAMP |
CURRENT_USER |
CURSOR |
DATE |
DAY |
DEALLOCATE |
DEC |
DECIMAL |
DECLARE |
DEFAULT |
DEFERRABLE |
DEFERRED |
DELETE |
DESC |
DESCRIBE |
DESCRIPTOR |
DIAGNOSTICS |
DISCONNECT |
DISTINCT |
DOMAIN |
DOUBLE |
DROP |
ELSE |
END |
END-EXEC |
ESCAPE |
EXCEPT |
EXCEPTION |
EXEC |
EXECUTE |
EXISTS |
EXTERNAL |
EXTRACT |
FALSE |
FETCH |
FIRST |
FLOAT |
FOR |
FOREIGN |
FOUND |
FROM |
FULL |
GET |
GLOBAL |
GO |
GOTO |
GRANT |
GROUP |
HAVING |
HOUR |
IDENTITY |
IMMEDIATE |
IN |
INDICATOR |
INITIALLY |
INNER |
INPUT |
INSENSITIVE |
INSERT |
INT |
INTEGER |
INTERSECT |
INTERVAL |
INTO |
IS |
ISOLATION |
JOIN |
KEY |
LANGUAGE |
LAST |
LEADING |
LEFT |
LEVEL |
LIKE |
LOCAL |
LOWER |
MATCH |
MAX |
MIN |
MINUTE |
MODULE |
MONTH |
NAMES |
NATIONAL |
NATURAL |
NCHAR |
NEXT |
NO |
NOT |
NULL |
NULLIF |
NUMERIC |
OCTET_LENGTH |
OF |
ON |
ONLY |
OPEN |
OPTION |
OR |
ORDER |
OUTER |
OUTPUT |
OVERLAPS |
PAD |
PARTIAL |
POSITION |
PRECISION |
PREPARE |
PRESERVE |
PRIMARY |
PRIOR |
PRIVILEGES |
PROCEDURE |
PUBLIC |
READ |
REAL |
REFERENCES |
RELATIVE |
RESTRICT |
REVOKE |
RIGHT |
ROLLBACK |
ROWS |
SCHEMA |
SCROLL |
SECOND |
SECTION |
SELECT |
SESSION |
SESSION_USER |
SET |
SMALLINT |
SOME |
SPACE |
|
SQL |
SQLCODE |
SQLERROR |
SQLSTATE |
SUBSTRING |
SUM |
SYSTEM_USER |
TABLE |
TEMPORARY |
THEN |
TIME |
TIMESTAMP |
TIMEZONE_HOUR |
TIMEZONE_MINUTE |
TO |
TRAILING |
TRANSACTION |
TRANSLATE |
TRANSLATION |
TRIM |
TRUE |
UNION |
UNIQUE |
UNKNOWN |
UPDATE |
UPPER |
USAGE |
USER |
USING |
VALUE |
VALUES |
VARCHAR |
VARYING |
VIEW |
WHEN |
WHENEVER |
WHERE |
WITH |
WORK |
WRITE |
YEAR |
ZONE |
日付に対するクエリーオブクエリーの使用
QueryNew 関数を使用してクエリーオブジェクトを作成し、列に日付定数を挿入した場合、その日付は、クエリーオブジェクトにクエリーオブクエリーを適用するまで、クエリーオブジェクトの内部に文字列として保存されます。クエリーオブジェクトにクエリーオブクエリーを適用すると、文字列表現 が 日付オブジェクトに変換されます。
クエリーオブクエリーでは、次のように SQL および ODBC 形式の次の日付定数がサポートされています。
- SQL 形式：次のいずれかの形式の日付、時刻、またはタイムスタンプ。
- 日付文字列： yyyy -mm-dd（例：1955-06-13）
- 時刻文字列：hh:mm:ss[.nnn]（例：14:34:30.75）
- タイムスタンプ文字列：yyyy-mm-dd hh:mm:ss[.nnn]（例」1924-01-14 12:00:00.000）
- ODBC 形式：次のいずれかの形式の日付、時刻、またはタイムスタンプ。
- 日付文字列：{d 'value'}（例：{d '2004-07-06'}）
- 時刻文字列：{t 'value'}（例：{t '13:45:30'}）
- タイムスタンプ文字列：{ts 'value'}（例：{ts '2004-07-06 13:45:30'}）
- 日付文字列：{d 'value'}（例：{d '2004-07-06'}）
注意：日付を元の形式に変換するには、DateFormat 関数を使用して「mm/dd/yy」マスクを適用します。 |
クエリーオブクエリーのパフォーマンスについて
クエリーオブクエリーのパフォーマンスは、データベースから直接アクセスされた単一テーブルのクエリーオブジェクトでは高くなります。これは、データベースからアクセスされたクエリーオブジェクトのメタ情報が ColdFusion で保管されるからです。
SQL を結合するクエリーを使用する場合、クエリーオブクエリーは、次のようなパフォーマンスになります。
2 つの列参照または定数の間に 1 つの等号のみを使用した単純な結合では、クエリーオブクエリーは効率的です。次に例を示します。
SELECT T1.a, b, c, d FROM T1, T2 WHERE T1.a = T2.a
述部に複数の式が含まれる結合では、クエリーオブクエリーの効率は低下します。次に例を示します。
WHERE T1.a = T2.a AND T1.b + T1.c = T2.b + T2.c
クエリーオブクエリーの処理について
クエリーオブクエリーでは、次の処理が行えます。
- 列の比較
- 参照により渡されるクエリー
- 複合オブジェクト
データ型が異なる列の比較
ColdFusion MX 7 以降では、データ型の異なる列を比較する機能が拡張されています。
オペランドの一方が既知の型を持つ列である場合（列の型が不明になるのは定数のみです）、クエリーオブクエリーは、型が不明な定数に対して、メタデータを持つオペランドの型を適用しようとします。適用可能な型の組み合わせは次のとおりです。
- バイナリ、文字列
- 日付、文字列
- 数値、 bigdecimal
- ブール、数値
つまり、バイナリと文字列の間で適用することはできますが、日付と文字列の間では適用できません。
両方のオペランドが既知のデータ型である場合、その型は同じであることが必要です。唯一の例外として、integer、float、double の間では強制的に適用できます。
両方のオペランドが定数の場合は、まず制約の厳しい型を、次に制約の緩やかな型を値に適用しようとします。
- まずバイナリを試し、次に 文字列 を試す。
- まず日付を試し、次に 文字列 を試す。
- まずブール値を試し、次に数値を試す。
