PostgreSQL サブクエリ
サブクエリは内部クエリまたはネストクエリとも呼ばれ、PostgreSQL クエリの WHERE 句にクエリ文を埋め込むことを指します。
SELECT 文のクエリ結果を、別の文の入力値として使用できます。
サブクエリは SELECT、INSERT、UPDATE、DELETE 文と一緒に使用でき、=、<、>、>=、<=、IN、BETWEEN などの演算子を使用できます。
以下はサブクエリが従わなければならないいくつかのルールです:
サブクエリは括弧で囲む必要があります。
サブクエリは SELECT 句内に1つの列のみを持つことができます。ただし、メインクエリに複数の列があり、サブクエリの選択された列と比較する場合は除きます。
ORDER BY はサブクエリ内では使用できませんが、メインクエリでは ORDER BY を使用できます。サブクエリ内では GROUP BY を使用でき、機能は ORDER BY と同じです。
サブクエリが複数行を返す場合は、IN 演算子などの複数値演算子と一緒にのみ使用できます。
BETWEEN 演算子はサブクエリと一緒に使用できませんが、BETWEEN はサブクエリ内で使用できます。
SELECT 文でのサブクエリの使用
サブクエリは通常 SELECT 文と一緒に使用されます。基本構文は次のとおりです:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
例
COMPANY テーブルを作成します(COMPANY SQL ファイルをダウンロード)、データ内容は次のとおりです:
サンプルdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
では、SELECT 文でサブクエリを使用してみましょう:
サンプルdb=# SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;
結果は次のとおりです:
id | name | age | address | salary ----+-------+-----+-------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows)
INSERT 文でのサブクエリの使用
サブクエリは INSERT 文と一緒に使用することもできます。INSERT 文はサブクエリが返したデータを使用して別のテーブルに挿入します。
サブクエリで選択されたデータは、任意の文字、日付、または数値関数で変更できます。
基本構文は次のとおりです:
INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]
例
COMPANY_BKP の構造は COMPANY テーブルと似ており、同じ CREATE TABLE を使用して作成できますが、テーブル名を COMPANY_BKP に変更するだけです。では、COMPANY テーブル全体を COMPANY_BKP にコピーします。構文は次のとおりです:
サンプルdb=# INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;
UPDATE 文でのサブクエリの使用
サブクエリは UPDATE 文と組み合わせて使用できます。UPDATE 文でサブクエリを使用すると、テーブルの1つまたは複数の列が更新されます。
基本構文は次のとおりです:
UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
例
COMPANY_BKP テーブルがあり、これは COMPANY テーブルのバックアップであるとします。
次の例では、COMPANY テーブル内の AGE が 27 より大きいすべての顧客の SALARY を元の 0.50 倍に更新します:
サンプルdb=# UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );
これは2行に影響し、最終的な COMPANY テーブルのレコードは次のとおりです:
id | name | age | address | salary ----+-------+-----+-------------+-------- 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 1 | Paul | 32 | California | 10000 5 | David | 27 | Texas | 42500 (7 rows)
DELETE 文でのサブクエリの使用
サブクエリは、上記の他の文と同様に、DELETE 文と組み合わせて使用できます。
基本構文は次のとおりです:
DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
例
COMPANY_BKP テーブルがあり、これは COMPANY テーブルのバックアップであるとします。
次の例では、COMPANY テーブル内の AGE が 27 以上であるすべての顧客レコードを削除します:
サンプルdb=# DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );
これは2行に影響し、最終的な COMPANY テーブルのレコードは次のとおりです:
id | name | age | address | salary ----+-------+-----+-------------+-------- 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 5 | David | 27 | Texas | 42500 (6 rows)その他の拡張