データベース内のテーブルから行を検索します。
Synopsis
SELECT [DISTINCT | ALL] [TOP parameter]
select-item
[INTO host-variable-list]
FROM [%INORDER | %FULL] table-ref [[AS] t-alias]
{,table-ref [[AS] t-alias]}
[WHERE condition-expression]
[GROUP BY scalar-expression]
[HAVING condition-expression]
[ORDER BY item-order-list [ASC | DESC] ]
select-item ::=
[t-alias.]*
[t-alias.]scalar-expression [[AS] c-alias]
{,[t-alias.]scalar-expression [[AS] c-alias]}
Caché は、
SELECT の成功か失敗かを示した、ステータス変数 SQLCODE を返します。また、
SELECT 演算は、%ROWCOUNT ローカル変数を、選択された行の番号に設定します。
SELECT 文は独立したクエリとして使用することも、大きな文の一部、つまりサブクエリとして使用することもできます。サブクエリは括弧で囲まれます。
-
select-item リストは、テーブルから検索するか生成する 1 つまたは複数の項目 (
select-item 引数) のコンマ区切りリストです。通常、これらの項目は、テーブルの列名です。
select-item は、1 つまたは複数の個別の項目を指定するスカラ式、もしくはベース・テーブルのすべての列を参照するアスタリスク (*) のうちのどちらかで構成されています。
-
FROM 節 は、行を検索する 1 つまたは複数のテーブル、ビュー、サブクエリ、または
JOIN 式を指定します。
以下のオプションの節は、FORM 節が返す仮想テーブルで動作します。すべてオプションですが、使用する場合は正しい順序で指定する必要があります。
-
WHERE 節 は、行が一致する 1 つまたは複数の条件制約を指定します。制約の詳細については、"
条件式" を参照してください。したがって、WHERE 節は、条件制約を指定するすべてのレコードを返します。
-
GROUP BY 節 は、コンマで区切られた列のリストを指定します。列と一致する値を使用してクエリの結果セットをサブセットに組み込み、返された行の順番を決めます。GROUP BY により、列と同様スカラ式も可能になります。
-
HAVING 節 は、1 つまたは複数の条件制約を指定します。制約の詳細については、"
条件式" を参照してください。通常、GROUP BY 節で指定した各グループで動作します。したがって、HAVING 節は、一般に、条件制約を満たすそれぞれの値に 1 つのレコードを返します。
-
DISTINCT キーワードは、フィールド値の重複を取り除きます。
SELECT DISTINCT Home_State FROM Sample.Person
これによって、各個別の州名に対して、1 つの行を返します。DISTINCT は、個々の行ではなく個別の値を返すので、複数の項目を指定すると、すべての項目に個別な値をすべて検索します。以下はその例です。
SELECT DISTINCT Home_State,Age FROM Sample.Person
Home_State と Age の値の異なる組み合わせを含むレコードをすべて検索します。
DISTINCT 節で指定された列が NULL 値を含む場合、DISTINCT は、以下の例に示すように、NULL 値を持つ 1 行を返します。
SELECT DISTINCT FavoriteColors FROM Sample.Person
定義上、すべての行が個別の一意の識別子を含むので、構文
DISTINCT * は適切ですが意味を持ちません。
DISTINCT 節は、1 つまたは複数の集約フィールドを持つように指定できます。
SELECT DISTINCT Age, AVG(Age) FROM Sample.Person
埋め込み SQL で DISTINCT キーワードを指定しても無意味です。埋め込み SQL の
SELECT はデータ行を常に 1 行しか返さないためです。
TOP キーワードは、
SELECT 文が、指定された行数のみを返すように指定します。返された仮想テーブルの "一番上" から、指定された行数分を返します。パラメータ引数を使用すると、返された行が変数で指定されます。リテラル引数は返す行のリテラル数を指定します。
TOP キーワードは、サブクエリでは使用できません。
以下のクエリは、データベースに格納される順番で Sample.Person から取得する最初の 20 行を返します。
SELECT TOP 20 Home_State,Name FROM Sample.Person
以下のクエリは、データベースに格納される順序で Sample.Person から取得する最初の 20 個の個別 Home_State 値を返します。前のクエリによって返された重複した Home_State 値を持つ行は、ここでスキップされます。
SELECT DISTINCT TOP 20 Home_State FROM Sample.Person
以下のクエリは、最初の 20 個の個別 Home_State 値を返します。しかし、ここでは、"最初" の行は、Sample.Person 内の行のすべての順序を ORDER BY 節に反映します。
SELECT DISTINCT TOP 20 Home_State FROM Sample.Person
ORDER BY Home_State
埋め込み SQL で TOP キーワードを指定しても無意味です。埋め込み SQL の
SELECT はデータ行を常に 1 行しか返さないためです。
-
コンマで区切られた、1 つまたは複数の列名 (フィールド) のリスト。
SELECT Name,Age FROM Sample.Person
FROM 節で 1 つまたは複数のテーブルまたはビューを指定するとき、次の 2 つの例に示すように、ピリオドを使用して、
select-item の一部としてテーブル名 (またはテーブル名エイリアス) を含める必要があります。
SELECT Sample.Company.Name, Sample.Person.Name
FROM Sample.Person, Sample.Company
SELECT p.Name, c.Name
FROM Sample.Person AS p, Sample.Company AS c
SELECT Home_City,Home_State FROM Sample.Person
-
テーブル内のすべての列を選択する、アスタリスク構文 (*)。
SELECT * FROM Sample.Person
select-item がアスタリスク構文を使用する場合、テーブルのすべての列の参照を指定します。アスタリスク構文は、修飾、未修飾のどちらでもかまいません。
-
-
select-item がテーブル名 (またはテーブル名エイリアス) およびピリオド (.) を接頭語として使用することによって修飾される場合、
select-item は、指定されたテーブル内のすべての列を選択します。修飾されたアスタリスク構文は、別のテーブルの別の選択項目と結合できます。
以下の例では、
select-item は、1 つのテーブルからすべての列を選択する修飾されたアスタリスク構文と、別のテーブルの列名のリストで構成されます。
SELECT TOP 50 Sample.Person.*, Sample.Company.Name,
Sample.Company.Home_State
FROM Sample.Person, Sample.Company
Note:
SELECT * は、アプリケーションの開発およびデバッグ中に非常に便利な Caché SQL を完全にサポートしている部分です。しかし、稼動中のアプリケーションでは、好まれるプログラミングの実践は、アスタリスク構文形式を使用するよりも、明示的に選択したフィールドを一覧表示することです。明示的にリストしたフィールドは、アプリケーションをわかりやすく簡単に理解し、簡単に維持し、名前によってフィールドを簡単に検索できるようにします。
-
-
1 つまたは複数の算術式を含む
select-item リスト。これらの算術演算は、通常、1 つまたは複数のフィールド値を使用します。
SELECT Name, Age, Age - AVG(Age) FROM Sample.Person
SELECT Name, Age, 9 - 6 FROM Sample.Person
-
SELECT Name,UCASE(Name) FROM Sample.Person
-
-
-
-
-
-
-
LIST — 指定された列内で使用されるすべての値のコンマ区切りリスト。
-
XMLAGG — 指定された列内で使用されるすべての値の連結文字列。
集約関数は、常に単独の値を返します。集約関数の引数は、以下のいずれかになります。
-
単独の列名 — クエリによって選択された行の NULL でないすべての値に対して、集約を計算します。
SELECT AVG(Age) FROM Sample.Person
-
SELECT SUM(Age) / COUNT(*) FROM Sample.Person
-
ワイルドカード (*) — テーブルの行数を計算するために、COUNT 関数とともに使用されます。
SELECT COUNT(*) FROM Sample.Person
-
選択個別関数 — 余剰値を消去することで、集約を計算します。
SELECT COUNT(DISTINCT Home_State) FROM Sample.Person
-
ANSI SQL は単独の
SELECT 文内での列名と集約関数の組み合わせを許可しませんが、Caché SQL は以下を許可することにより、標準を拡張しています。
SELECT Name, COUNT(DISTINCT Home_State) FROM Sample.Person
-
%FOREACH を使用する集約関数。これによって、単独または複数の列の各個別の値に対して集約関数が計算されます。
SELECT DISTINCT Home_State, AVG(Age %FOREACH(Home_State))
FROM Sample.Person
-
%AFTERHAVING を使用する集約関数。これよって、HAVING 節で指定されるサブ母集団で集約関数が計算されます。
SELECT AVG(Age %AFTERHAVING) FROM Sample.Person
HAVING (Age > AVG(Age))
以上の文は、データベース内の年齢が平均よりも高いすべての人に対して平均年齢を返します。
-
Caché ObjectScript 外部関数は、データベースの列上でオペレーティングを呼び出します。
SELECT $$REFORMAT^ABC(name)FROM MyTable
外部関数を使用して % ルーチン (% 文字で始まる名前を持つルーチン) を呼び出せません。実行しようとすると SQLCODE -373 エラー・コードが発行されます。
-
SELECT UCASE('fred') FROM Sample.Person
SELECT 7 * 7, 7 * 8 FROM Sample.Person
データが参照されない場合でも、指定されたテーブルは存在する必要があります。このタイプのクエリでは、行数は、指定されたテーブルの行数に対応して返されます。1 行のみを返すには、DISTINCT キーワードを使用します。
AS 節を省略した場合、Caché SQL は、"Expression_1" または "Aggregate_3" などの一意の列名を提供します。ここの番号は、クエリによって返される、フィールドの列番号を示します。AS キーワードは、リスト内の各
select-item の直後に続きます。AS キーワードそれ自体は必須項目ではありませんが、構文を明確にします。
以下の例では、テーブル列 "Home_State" は、"US_State_Abbrev" という名前に変更されます。
SELECT Name, Home_State AS US_State_Abbrev
FROM Sample.Person
以下の例では、AVG 関数で作成された集約フィールド列は、"AvgAge" という名前を付けられます。既定の名前は "Aggregate_3" (列 3 の集約フィールド) です。
SELECT Name, Age, AVG(Age) AS AvgAge FROM Sample.Person
以下の例は、AS キーワードがここで省略されていることを除き、前と同じです。このキーワードの使用は推奨されますが、必須ではありません。
SELECT Name, Age, AVG(Age) AvgAge FROM Sample.Person
FROM
table-ref 節は、1 つ、またはコンマ区切りの複数のテーブル名やビュー名 (もしくは両方) を指定する必須の節です。
テーブル名にエイリアス (
t-alias) を使用できます。オプションで AS キーワードを使用できます。この
t-alias は、フィールド名を指定するときにそのテーブルを示すために使用できます。詳細は、"
FROM" を参照してください。
以下の 3 つの
SELECT 文は、2 つの個別のテーブルに対する行カウントと、両方のテーブルを指定する
SELECT に対する行カウントを示しています。後者の例は、より大きなテーブル、デカルト積になります。この場合、1 番目のテーブルのそれぞれの行が、2 番目のテーブルのそれぞれの行に一致します。これは、
Cross Join という処理です。
SELECT COUNT(*)
FROM Sample.Company
SELECT COUNT(*)
FROM Sample.Vendor
SELECT COUNT(*)
FROM Sample.Company, Sample.Vendor
多くの場合、Cross Join の広範囲なデータ重複は望ましくありません。その他の Join タイプをお勧めします。Join は、FROM 節または WHERE 節のいずれかで指定されます。詳細は、"
JOIN" を参照してください。
Join 処理がコンマ区切りの
table-ref リストで実行される順序は、FROM 節内のオプションの %INORDER キーワードおよび %FULL キーワードを使用することにより最適化できます。詳細は、"
FROM" 節のリファレンス・ページを参照してください。
table-ref を指定するときに、オプションの AS キーワードを使用して、そのテーブル名またはビュー名のエイリアスを指定できます。
table1 AS t-alias1,table2 AS t-alias2,...
これは、以下に示すように、一般的には、ソース・テーブルで
select-item のフィールドを指定するために使用されます。
SELECT TOP 50 t1.*, t2.Name
FROM Sample.Person AS t1, Sample.Company AS t2
FROM 節で、希望のエイリアスを持つ各テーブルの名前の直後に、AS キーワードと割り当てる
t-alias を続けます。
AS キーワードそれ自体は必須項目ではありませんが、構文を明確にします。以下の例は、AS キーワードがここで省略されていることを除き、前と同じです。このキーワードの使用は推奨されますが、必須ではありません。
SELECT TOP 50 t1.*, t2.Name
FROM Sample.Person t1, Sample.Company t2
詳細は、"
WHERE" 節のリファレンス・ページを参照してください。
GROUP BY 節はクエリの結果行をとり、単独または複数のデータベース列によって結果行を個別のグループに分割します。
SELECT を GROUP BY 節と併せて使用するとき、GROUP BY フィールドの個別の各値に対して、1 行が検索されます。GROUP BY 節は概念的には Caché の拡張である %FOREACH と似ていますが、%FOREACH はクエリ全体を制約することなく、サブ母集団で集約の選択を許可するのに対し、GROUP BY はクエリ全体で実行します。以下はその例です。
SELECT Home_State, COUNT(Home_State) AS Population
FROM Sample.Person
GROUP BY Home_State
このクエリは、各個別の Home_State に対し 1 行を返します。
HAVING 節はグループで実行する WHERE 節と同じ働きをします。一般的に、GROUP BY 節または %AFTERHAVING キーワードとの組み合わせで使用します。HAVING 節は、クエリ選択から特定の行を適格とするか、もしくは不適格とします。適格な行とは、
condition-expression が真である行です。
condition-expression は、AND、OR、および NOT 演算子によってリンク可能な論理テスト (述語) のリストです。詳細は、"
HAVING" 節のリファレンス・ページを参照してください。
ORDER BY 節は、select-item、あるいは行が表示される順番を指定する項目のコンマ区切りリストが後続する、ORDER BY キーワードで構成されます。各項目は、オプションの ASC (昇順) や DESC (降順) を持ちます。既定は昇順です。ORDER BY 節は、クエリの結果に対してのみ使用できます。サブクエリである
SELECT 文 (例えば UNION 文) には使用できません。詳細は、"
ORDER BY" 節のリファレンス・ページを参照してください。
以下の例は、データベースのすべての行に選択されたフィールドを返し、年齢の昇順でこれらの行を並べます。
SELECT Home_State, Name, Age
FROM Sample.Person
ORDER BY Age
トランザクションは、READ COMMITTED または READ UNCOMMITTED のいずれかに定義されます。現在のトランザクションが READ UNCOMMITTED に定義されている場合、
SELECT は、コミット前の現在のトランザクション中に加えられた変更も含めて現在のデータの状態を返します。この変更は、後でロールバックされる場合もあります。
現在のトランザクションが READ COMMITTED に定義されている場合、
SELECT 文による動作は、状況により異なります。通常、
SELECT 文ではコミットされたデータのみが返されます。しかし、
SELECT 文に
DISTINCT キーワードや
GROUP BY 節が含まれる場合、
SELECT は、コミット前の現在のトランザクション中に加えられた変更も含めて現在のデータの状態を返します。
SELECT 内の集約関数でも、指定された列のデータの現在の状態は、コミットされていない変更分を含めて返されます。
以下の 4 つの例は、
SELECT 節の異なる組み合わせを使用して、同様のクエリを実行します。これらの節は、正しい順序で指定する必要があります。4 つの例ではすべて、Sample.Person テーブルから Name、Home_State、および Age の 3 つのフィールドが選択され、2 つのフィールド (AvgAge と AvgMiddleAge) が計算されています。
以下の例では、AvgAge 計算フィールドは、Sample.Person 内のすべてのレコードで計算されます。
HAVING 節は、AvgMiddleAge 計算フィールドを制御し、Sample.Person 内のすべてのレコードから 40 を超えるものの平均年齢を計算します。したがって、すべての行で AvgAge および AvgMiddleAge が同じ値になります。
ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
AVG(Age %AFTERHAVING) AS AvgMiddleAge
FROM Sample.Person
HAVING Age > 40
ORDER BY Home_State
以下の例では、
WHERE 節は、指定された北東の 7 つの州に選択肢を限定します。AvgAge 計算フィールドは、これらの Home_States のレコードで計算されます。
HAVING 節は、指定された Home_States 内のレコードで 40 を超える平均年齢を計算して、AvgMiddleAge 計算フィールドを設定します。したがって、各行は AvgAge および AvgMiddleAge と同じ値を持ちます。
ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
AVG(Age %AFTERHAVING) AS AvgMiddleAge
FROM Sample.Person
WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
HAVING Age > 40
ORDER BY Home_State
GROUP BY 節は、AvgAge 計算フィールドが、各 Home_State グループに対して個別に計算されるようにします。また、
GROUP BY 節は、各 Home_State で使用される最初のレコードに対する出力表示を制限します。
HAVING 節は、各 Home_State グループ内で 40 を超える平均年齢を計算して、AvgMiddleAge 計算フィールドを設定します。
ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
AVG(Age %AFTERHAVING) AS AvgMiddleAge
FROM Sample.Person
GROUP BY Home_State
HAVING Age > 40
ORDER BY Home_State
WHERE/GROUP BY/HAVING/ORDER BY
WHERE 節は、指定された北東の 7 つの州に選択肢を限定します。
GROUP BY 節は、AvgAge 計算フィールドが、7 つの各 Home_State グループに対して個別に計算されるようにします。また、
GROUP BY 節は、指定された Home_State で使用される最初のレコードに対する出力表示を制限します。
HAVING 節は、7 つの各 Home_State 内で 40 を超える平均年齢を計算して、AvgMiddleAge 計算フィールドを設定します。
ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
AVG(Age %AFTERHAVING) AS AvgMiddleAge
FROM Sample.Person
WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
GROUP BY Home_State
HAVING Age > 40
ORDER BY Home_State