MENU
初心者からプロまでクラウドを中心としたインフラ技術の活用方法を詳しく解説します。
  1. ホーム
  2. PostgreSQL
  3. 【PostgreSQL】SQL文【Part.11】

【PostgreSQL】SQL文【Part.11】

PostgreSQL データベース

今回は SQL コマンド（SQL 文）について解説します。

分かりにくいかもしれませんが、目的別とコマンドの解説を記載しています。

 

 

PostgreSQL の SQL コマンド一覧

PostgreSQL の SQL コマンド一覧は以下のメタコマンドで確認できます。

postgres=# \h 
Available help: 
  ABORT                            CREATE FUNCTION                  DROP TABLE 
  ALTER AGGREGATE                  CREATE GROUP                     DROP TABLESPACE 
  ALTER COLLATION                  CREATE INDEX                     DROP TEXT SEARCH CONFIGURATION 
  ALTER CONVERSION                 CREATE LANGUAGE                  DROP TEXT SEARCH DICTIONARY 
  ALTER DATABASE                   CREATE OPERATOR                  DROP TEXT SEARCH PARSER 
  ALTER DEFAULT PRIVILEGES         CREATE OPERATOR CLASS            DROP TEXT SEARCH TEMPLATE 
  ALTER DOMAIN                     CREATE OPERATOR FAMILY           DROP TRIGGER 
  ALTER EXTENSION                  CREATE ROLE                      DROP TYPE 
  ALTER FOREIGN DATA WRAPPER       CREATE RULE                      DROP USER 
  ALTER FOREIGN TABLE              CREATE SCHEMA                    DROP USER MAPPING 
  ALTER FUNCTION                   CREATE SEQUENCE                  DROP VIEW 
  ALTER GROUP                      CREATE SERVER                    END 
  ALTER INDEX                      CREATE TABLE                     EXECUTE 
  ALTER LANGUAGE                   CREATE TABLE AS                  EXPLAIN 
  ALTER LARGE OBJECT               CREATE TABLESPACE                FETCH 
  ALTER OPERATOR                   CREATE TEXT SEARCH CONFIGURATION GRANT 
  ALTER OPERATOR CLASS             CREATE TEXT SEARCH DICTIONARY    INSERT 
  ALTER OPERATOR FAMILY            CREATE TEXT SEARCH PARSER        LISTEN 
  ALTER ROLE                       CREATE TEXT SEARCH TEMPLATE      LOAD 
  ALTER SCHEMA                     CREATE TRIGGER                   LOCK 
  ALTER SEQUENCE                   CREATE TYPE                      MOVE 
  ALTER SERVER                     CREATE USER                      NOTIFY 
  ALTER TABLE                      CREATE USER MAPPING              PREPARE 
  ALTER TABLESPACE                 CREATE VIEW                      PREPARE TRANSACTION 
  ALTER TEXT SEARCH CONFIGURATION  DEALLOCATE                       REASSIGN OWNED 
  ALTER TEXT SEARCH DICTIONARY     DECLARE                          REINDEX 
  ALTER TEXT SEARCH PARSER         DELETE                           RELEASE SAVEPOINT 
  ALTER TEXT SEARCH TEMPLATE       DISCARD                          RESET 
  ALTER TRIGGER                    DO                               REVOKE 
  ALTER TYPE                       DROP AGGREGATE                   ROLLBACK 
  ALTER USER                       DROP CAST                        ROLLBACK PREPARED 
  ALTER USER MAPPING               DROP COLLATION                   ROLLBACK TO SAVEPOINT 
  ALTER VIEW                       DROP CONVERSION                  SAVEPOINT 
  ANALYZE                          DROP DATABASE                    SECURITY LABEL 
  BEGIN                            DROP DOMAIN                      SELECT 
  CHECKPOINT                       DROP EXTENSION                   SELECT INTO 
  CLOSE                            DROP FOREIGN DATA WRAPPER        SET 
  CLUSTER                          DROP FOREIGN TABLE               SET CONSTRAINTS 
  COMMENT                          DROP FUNCTION                    SET ROLE 
  COMMIT                           DROP GROUP                       SET SESSION AUTHORIZATION 
  COMMIT PREPARED                  DROP INDEX                       SET TRANSACTION 
  COPY                             DROP LANGUAGE                    SHOW 
  CREATE AGGREGATE                 DROP OPERATOR                    START TRANSACTION 
  CREATE CAST                      DROP OPERATOR CLASS              TABLE 
  CREATE COLLATION                 DROP OPERATOR FAMILY             TRUNCATE 
  CREATE CONVERSION                DROP OWNED                       UNLISTEN 
  CREATE DATABASE                  DROP ROLE                        UPDATE 
  CREATE DOMAIN                    DROP RULE                        VACUUM 
  CREATE EXTENSION                 DROP SCHEMA                      VALUES 
  CREATE FOREIGN DATA WRAPPER      DROP SEQUENCE                    WITH 
  CREATE FOREIGN TABLE             DROP SERVER 
postgres=#

 

 

SQL コマンドの分類

  • DDL（Data Definition Language、データ定義言語）
  • DML（Data Manipulation Language、データ操作言語）
  • DCL（Data Control Language、データ制御言語）

 

DDL（Data Definition Language、データ定義言語）

  • CREATE　テーブル、インデックス作成
  • DROP　テーブル、インデックス削除
  • ALTER　テーブルやインデックスの定義を変更
  • TRUNCATE　テーブルからレコードを削除

 

DML（Data Manipulation Language、データ操作言語）

  • SELECT　データを検索
  • INSERT　データを挿入
  • UPDATE　データを更新
  • DELETE　データを削除

 

DCL（Data Control Language、データ制御言語）

  • GRANT　アクセス権限の付与
  • REVOKE　アクセス権限の取り消し
  • BEGIN　トランザクションの開始
  • COMMIT　トランザクションの確定
  • ROLLBACK　トランザクションの取り消し

 

 

 

 

データベースを作成する

データベースを作成します。

 

まずは現在のデータベース一覧を表示します。

-bash-4.2$ psql -l 
                                  List of databases 
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges 
———–+———-+———-+————-+————-+———————– 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + 
           |          |          |             |             | postgres=CTc/postgres 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + 
           |          |          |             |             | postgres=CTc/postgres 
(3 rows) 
 
-bash-4.2$

 

 

 

psql コマンドでデフォルトのデータベースに接続します。

-bash-4.2$ psql 
psql (9.2.24) 
Type “help” for help. 
 
postgres=#

 

 

データベース（testdb）を作成します。

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=#

 

 

データベースが作成されたことを確認します。

postgres=# \l 
                                  List of databases 
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges 
———–+———-+———-+————-+————-+———————– 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + 
           |          |          |             |             | postgres=CTc/postgres 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + 
           |          |          |             |             | postgres=CTc/postgres 
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows) 
 
postgres=#

 

 

新しく作成されたデータベース（testdb）にアクセスします。

postgres=# \c testdb 
You are now connected to database “testdb” as user “postgres”. 
testdb=#

 

 

テーブルを作成してデータを挿入する

新規作成したデータベース（testdb）に新規テーブル（testtable）を作成します。

testdb=# CREATE TABLE testtable ( 
testdb(#     no INTEGER PRIMARY KEY, 
testdb(#     name VARCHAR(256) NOT NULL 
testdb(# ); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index “testtable_pkey” for table “testtable” 
CREATE TABLE 
testdb=#

 

 

テーブルにデータを挿入します。

testdb=# INSERT INTO testtable VALUES(1,’高橋’);
INSERT 0 1
testdb=#

 

 

データを確認します。

testdb=# SELECT * FROM testtable; 
 no | name 
—-+—— 
  1 | 高橋 
(1 row) 
 
testdb=#

 

 

テーブルにデータを追加する

更にテーブルにデータを追加します。

testdb=# INSERT INTO testtable VALUES(2,’山田’); 
INSERT 0 1 
testdb=# INSERT INTO testtable VALUES(3,’鈴木’); 
INSERT 0 1 
testdb=# INSERT INTO testtable VALUES(4,’佐藤’); 
INSERT 0 1 
testdb=# INSERT INTO testtable VALUES(5,’中村’); 
INSERT 0 1 
testdb=# INSERT INTO testtable VALUES(6,’山本’); 
INSERT 0 1 
testdb=#

 

 

データを確認します。

testdb=# SELECT * FROM testtable; 
 no | name 
—-+—— 
  1 | 高橋 
  2 | 山田 
  3 | 鈴木 
  4 | 佐藤 
  5 | 中村 
  6 | 山本 
(6 rows) 
 
testdb=#

 

 

 

 

 

複雑な SELECT 文の結果を仮想のテーブルのように利用したい（ビュー、VIEW）

SELECT 文の結果を基に新しいテーブルを作成したい場合はビューを作成します。

ビューへの問い合わせが発生した場合、そのビューを作成する SELECT 文が実行されます。

 

CREATE TABLE ～ と CREATE VIEW ～ の違い

  • CREATE TABLE ～　←　CREATE TABLE ～ で作成したテーブルは、テーブルを作成する基になったテーブルとは関連性がなくなります。
  • CREATE VIEW ～　←　ビューは問い合わせを行うたびに、元のテーブルを参照します。

 

 

ユーザーを作成、確認する（ユーザー管理）

ユーザーの作成や確認方法です。

 

最初に現在のユーザーを確認します。

postgres=# \du 
                             List of roles 
 Role name |                   Attributes                   | Member of 
———–+————————————————+———– 
 postgres  | Superuser, Create role, Create DB, Replication | {} 
 
postgres=#

 

 

ユーザー test01 を作成します。

postgres=# CREATE USER test01;
CREATE ROLE
postgres=#

 

 

ユーザー test01 を確認します。

postgres=# \du 
                             List of roles 
 Role name |                   Attributes                   | Member of 
———–+————————————————+———– 
 postgres  | Superuser, Create role, Create DB, Replication | {} 
 test01    |                                                | {} 
 
postgres=#

 

※まだ test01 には権限が割り当てられていません。

 

 

ユーザーに権限を割り当てる

ユーザーに権限を割り当てます。

 

対象のデータベースに変更します。

postgres=# \c testdb
You are now connected to database “testdb” as user “postgres”.
testdb=#

 

 

 

現在のテーブルを確認します。

testdb=# \dt 
           List of relations 
 Schema |   Name    | Type  |  Owner 
——–+———–+——-+———- 
 public | testtable | table | postgres 
(1 row) 
 
testdb=#

 

 

現在の testtable に対する権限を確認します。

まだ何も権限が割り当てられていません。

testdb=# \z 
                             Access privileges 
 Schema |   Name    | Type  | Access privileges | Column access privileges 
——–+———–+——-+——————-+————————– 
 public | testtable | table |                   | 
(1 row) 
 
testdb=#

 

 

 

テーブル testtable に対して SELECT, INSERT, UPDATE, DELETE 権限をアカウント test01 に割り当てます。

testdb=# GRANT SELECT, INSERT, UPDATE, DELETE on testtable TO test01;
GRANT
testdb=#

 

 

 

権限を確認します。

testdb=# \z 
                                 Access privileges 
 Schema |   Name    | Type  |     Access privileges     | Column access privileges 
——–+———–+——-+—————————+————————– 
 public | testtable | table | postgres=arwdDxt/postgres+| 
        |           |       | test01=arwd/postgres      | 
(1 row) 
 
testdb=#

 

 

権限の確認方法

権限の確認方法です。

testdb=# \z 
                                 Access privileges 
 Schema |   Name    | Type  |     Access privileges     | Column access privileges 
——–+———–+——-+—————————+————————– 
 public | testtable | table | postgres=arwdDxt/postgres+| 
        |           |       | test01=arwd/postgres      | 
(1 row) 
 
testdb=#

 

  • test01=arwd/postgres　←　test01 に対して「arwd」の権限が割り当てられています。権限を付与したのは「postgres」アカウントです。
  • a　←　INSERT 権限
  • r　←　SELECT 権限
  • w　←　UPDATE 権限
  • d　←　DELETE 権限
  • D　←　TRUNCATE 権限
  • x　←　REFERENCES 権限
  • t　←　TRIGGER 権限
  • arwdDxt　←　ALL すべての権限

 

 

 

 

 

自動的にシーケンス番号を割り振る（SEQUENCE）

CREATE SEQUENCE コマンドでシーケンス番号を設定します。

  • シーケンス　←　testsequence
  • CACHE　←　メモリに格納するデータ量を指定します。この範囲内（10まで）なら高速でデータにアクセスが可能になります。
  • MAXVALUE　←　シーケンスの最大値を設定します。この場合は10が限界値です。
  • NO CYCLE　←　限界値（10）に到達したら終了します。（それ以上入れるとエラーになります）

postgres=# CREATE SEQUENCE testsequence CACHE 10 MAXVALUE 10 NO CYCLE;
CREATE SEQUENCE
postgres=#

 

 

値を設定します。

postgres=# SELECT setval(‘testsequence’, 1); 
 setval 
——– 
       1 
(1 row) 
 
postgres=#

 

現在の値（1）を確認します。

postgres=# SELECT currval(‘testsequence’); 
 currval 
——— 
        1
(1 row) 
 
postgres=#

 

 

nextval で次の値を取得し、自動的に値が増加していることを確認します。

postgres=# SELECT nextval(‘testsequence’); 
 nextval 
——— 
        2 
(1 row) 
 
postgres=# SELECT nextval(‘testsequence’); 
 nextval 
——— 
        3 
(1 row) 
 
postgres=#

 

 

限界値を超えたらエラーが出るのか確認します。

postgres=# SELECT nextval(‘testsequence1’); 
 nextval 
——— 
       8 
(1 row) 
 
postgres=# SELECT nextval(‘testsequence1’); 
 nextval 
——— 
       9 
(1 row) 
 
postgres=# SELECT nextval(‘testsequence1’); 
 nextval 
——— 
      10 
(1 row) 
 
postgres=# SELECT nextval(‘testsequence1’); 
ERROR:  nextval: reached maximum value of sequence “testsequence1” (10) 
postgres=#

 

 

 

 

指定したテーブル（ビュー）に適用するルールを定義したい

CREATE RULE により、指定したテーブル（ビュー）に適用するルールを定義することができます。

その結果、テーブルに対する挿入、更新、削除時に実行される別の処理を定義できます。

 

ルールを作成する

【例】

CREATE RULE ルール名 AS ON INSERT TO ビュー名 DO INSTED NOTHING;

 

ルールを削除する

DROP RULE ルール名;

 

 

 

 

SQL 文でのパターンマッチング

UNIX、LINUX では「正規表現」を利用してパターンマッチングをしますが、PostgreSQL の SQL 文でも以下の 3つでパターンマッチングを利用できます。

  • LIKE 演算子　←　対象のカラム名 LIKE ‘パターン’
  • SIMILAR TO 演算子　←　対象のカラム名 SIMILAR TO ‘パターン’
  • POSIX 正規表現　←　対象のカラム名 ~ ‘パターン’

 

「LIKE 演算子」と「SIMILAR TO 演算子」の任意の文字列を表わす「%」を利用できます。

 

【例】

LIKE ‘%TEST%’　

 

  • TEST、11TEST、TTEST11　→　含まれます。

 

 

【例】

LIKE ‘TEST’　

 

  • TEST　→　含まれます。
  • 11TEST、TTEST11　→　含まれません。

 

 

【例】

~ ‘TEST’　

 

  • TEST、11TEST、TTEST11　→　含まれます。

 

 

 

CREATE

CREATE コマンドについて解説します。

 

CREATE SCHEMA

CREATE SCHEMA <スキーマ名> AUTHORIZATION <所有者名>;

 

【例】

CREATE SCHEMA TestSchema01 AUTHORIZATION TestUser01;

 

 

DROP

DROP コマンドについて解説します。

 

DROP DATABASE　データベースを削除する

データベースを削除します。

 

DROP TABLE <テーブル名> テーブルを削除する

データも含めてテーブルを削除します。

DROP TABLE <テーブル名>;

 

 

DROP ROLE（DROP USER）　データベースロール（データベースユーザー）を削除する

【データベースロールを削除する】

DROP ROLE ロール名;

 

【データベースユーザーを削除する】

DROP USER ロール名;

 

 

 

GRANT 

GRANT は権限を付与するコマンドです。

 

■主な付与できる権限

  • すべての権限を付与（ALL）
  • テーブルへのアクセス権限を付与（SELECT）
  • データベースへのアクセス権限を付与（CONNECT）
  • スキーマへのアクセス権限を付与（CREATE）
  • テーブルのデータをファイルにコピーする権限を付与（COPY）
  • 外部キーを作成する権限を付与（REFERENCES）
  • トリガーの作成を許可する（TRIGGER）

 

 

【テーブルへのアクセス権限を付与】

GRANT <権限> ON <テーブル名> TO <ユーザー名>;

 

【データベースへのアクセス権限を付与】

GRANT <権限> ON DATABASE <データベース名> TO <ユーザー名>;

 

【スキーマへのアクセス権限を付与】

GRANT <権限> ON SCHEMA <スキーマ名> TO <ユーザー名>;

 

 

REVOKE

REVOKE は GRANT コマンドとは逆に「権限を取り消す」コマンドです。

※revoke の意味は「取消す、廃止する、解約する」という意味です。

 

■主な取り消しできる権限

  • すべての権限を付与（ALL）
  • テーブルへのアクセス権限を付与（SELECT）
  • データベースへのアクセス権限を付与（CONNECT）
  • スキーマへのアクセス権限を付与（CREATE）
  • テーブルのデータをファイルにコピーする権限を付与（COPY）
  • 外部キーを作成する権限を付与（REFERENCES）
  • トリガーの作成を許可する（TRIGGER）

 

 

【テーブルへのアクセス権限を取消す】

REVOKE <権限> ON <テーブル名> FROM <ユーザー名>;

 

【データベースへのアクセス権限を取消す】

REVOKE <権限> ON DATABASE <データベース名> FROM <ユーザー名>;

 

【スキーマへのアクセス権限を取消す】

REVOKE <権限> ON SCHEMA <スキーマ名> FROM <ユーザー名>;

 

 

 

COUNT

COUNTはカラムのデータ件数を返します。

カラムの値の中は見ていません。データ件数をカウントしています。

カラムを指定した場合、値が「NULL」の場合はカウントされません。

COUNT(*)とアスタリスクですべてを指定した場合は「NULL」もカウントされます。

  • COUNT(カラム)　←　NULL カウントしない
  • COUNT(*)　←　NULL もカウントする

 

 

重複を排除する（DISTINCT）

重複を排除する場合は DISTINCT を使用します。

SELECT 文の結果に対して処理をする場合に使用します。

  • distinct　←　明確な、明瞭な、他と異なる

 

 

重複している行を削除したい（INTERSECT）【2つのSQL文】

INTERSECT を使用して、2つの SQL 文で重複した行を削除することができます。

  • intersect　←　交差する、横切る

 

【例】

<SQL文1> INTERSECT <SQL文2>

 

※ALL を付けると重複した行もすべて表示されます。

 

 

2つの SQL 文を足した行を表示したい（UNION）【2つのSQL文】

UNION を使用して、2つの SQL 文を足した行を表示できます。

  • union　←　結合する

 

【例】

<SQL文1> UNION <SQL文2>

 

※計算式としては、「SQL文1 ＋ SQL文2」の合計です。

※ALL を付けると重複した行は省略されます。

 

 

 

2つの SQL 文の差分を表示したい（EXCEPT）【2つのSQL文】

EXCEPT を使用して、2つの SQL 文の差分を表示することができます。

  • except　←　除外する

 

【例】

<SQL文1> EXCEPT <SQL文2>

 

※計算式としては、「SQL文1 – SQL文2」の差分です。（SQL文1の結果からSQL文2の結果を引いたもの）

※ALL を付けると重複した行もすべて表示されます。

 

 

 

SELECT 文の結果の表示数や範囲を指定したい（LIMIT OFFSET）

LIMIT OFFSET の組み合わせで SELECT 文で検索した結果の表示数や範囲を指定することができます。

 

testtableの内容です。

testdb=# SELECT * FROM testtable; 
 no | name 
—-+—— 
  1 | 高橋 
  2 | 山田 
  3 | 鈴木 
  4 | 佐藤 
  5 | 中村 
  6 | 山本 
(6 rows) 
 
testdb=#

 

 

「LIMIT 3 OFFSET 1」で 2 から 3 つのデータを表示しています。

testdb=# SELECT * FROM testtable no LIMIT 3 OFFSET 1; 
 no | name 
—-+—— 
  2 | 山田 
  3 | 鈴木 
  4 | 佐藤 
(3 rows) 
 
testdb=#

 

 

 

SELECT 文の結果に対して件数を指定して操作する（カーソル、CURSOR）

LIMIT でも同様に任意の位置からデータを取得することは可能ですが、カーソルの場合は、SELECT 文の結果に対して、任意の位置から件数を指定して操作したり、任意の場所から取り出したデータに対して操作をすることができます。

その場合は「カーソル（CURSOR）」を使用します。

 

 

 

カーソルの作成

DECLARE CURSOR コマンドでカーソルを作成します。

 

カーソルのオプション

  • INSENTIVE　←　デフォルト。カーソルから取得したデータが取得元テーブルの更新に影響をうけません。
  • SCROLL　←　順方向（0→1→2→3→4→5→6→7）・逆方向（7→6→5→4→3→2→1→0）どちらでもカーソルを動かせるようにします。
  • NO SCROLL　←　データの順方向（0→1→2→3→4→5→6→7）のみカーソルを動かせるようにします。逆方向に動かした場合はエラーになります。
  • WITH HOLD　←　トランザクションコミット後もカーソルを利用できます。
  • WITHOUT HOLD　←　トランザクション内でのみカーソルを利用できます。

 

 

 

カーソルを利用してテーブルから行を抽出する（FETCH）

FETCH はカーソルを利用してテーブルから行を抽出します。

FETCH はカーソルを移動させて対象のデータ1行を取り出したり、複数行のデータを取り出すことができます。

 

FETCH のオプション

  • FETCH NEXT　←　（デフォルト）次の行を取り出します。
  • FETCH PRIOR　←　1つ前の行を取り出します。
  • FETCH <count>　←　次の<count>行を取り出します。（複数行可）
  • FETCH ALL　←　残っている行をすべて取り出します。
  • FETCH FORWARD　←　次の行を取り出します。
  • FETCH FORWARD <count>　←　次の<count>行を取り出します。（複数行可）
  • FETCH FORWARD ALL　←　残っている行をすべて取り出します。
  • FETCH BACKWARD　←　1つ前の行を取り出します。
  • FETCH BACKWARD <count>　←　前の<count>行を取り出します。（複数行可）
  • FETCH BACKWARD ALL　←　現在行より前の行から最初まですべて取り出します。

 

 

 

パターンの一致（LIKE、SIMILAR TO、正規表現）

テーブルの中の値に一致する文字列があるかどうか検索する方法です。

  • LIKE 演算子
  • SIMILAR TO 演算子
  • 正規表現

 

【例】TESTという文字列を検索したい場合

  • TEST　←　完全一致。「TEST」のみ対象となります。
  • %TEST%　←　文字列のどこかに「TEST」という文字列が入っています。
  • TEST%　←　文字列の先頭が「TEST」で始まります。
  • %TEST　←　文字列の最後が「TEST」で終わります。

 

 

SQL コマンドの終了はセミコロン（;）

SQL コマンドはセミコロン（;）で終了します。

postgres=# SELECT * FROM user; 
 current_user 
————– 
 postgres 
(1 row) 
 
postgres=#

 

 

 

内部結合と外部結合と交差結合と自然結合

  • 内部結合（INNER JOIN）　←　結合するテーブルの双方に結合条件を満たしたデータ（値）がある場合に限り、行が結合されます。
  • 外部結合（OUTER JOIN）　←　結合するテーブルの双方に結合条件を満たしたデータ（値）がない場合でも結合が行われます。
  • 交差結合（CROSS JOIN）　←　結合するテーブルのすべての組み合わせのデータ（値）で結合が行われます。
  • 自然結合（NATURAL JOIN）　←　結合するテーブル同士の同じ列名を結合条件とした結合が行われます。

 

もっと簡単に言うと以下のようになるでしょうか。

  • 内部結合（INNER JOIN）　←　両方のテーブルに指定したカラムの一致データがある場合に結合する
  • 外部結合（OUTER JOIN）　←　両方のテーブルに指定したカラムの一致データがなくても結合する

 

 

外部結合の種類

  • 左外部結合（LEFT OUTER JOIN）
  • 右外部結合（RIGHT OUTER JOIN）

 

 

■どちらのテーブルが基準となるのか

  • LEFT OUTER JOIN　←　FROM の後ろにあるテーブルが左
  • RIGHT OUTER JOIN　←　JOIN の後ろにあるテーブルが右

 

 

■例（LEFT が基準になる）

testdb=# SELECT * FROM <左側テーブル> LEFT OUTER JOIN <右側テーブル> ON <左側テーブル>.busho_id = <右側テーブル>.busho_id; 

 

■例（RIGHT が基準になる）

testdb=# SELECT * FROM <左側テーブル> RIGHT OUTER JOIN <右側テーブル> ON <左側テーブル>.busho_id = <右側テーブル>.busho_id; 

 

 

 

実行例のための環境作成

「INNER JOIN」、「OUTER JOIN」、「FEFT OUTER JOIN」、「RIGHT OUTER JOIN」の違いを具体的に確認するため環境を作成します。

 

table01を作成します。

testdb=# CREATE TABLE table01 ( 
testdb(#     no INTEGER PRIMARY KEY, 
testdb(#     name VARCHAR(256) NOT NULL, 
testdb(#     busho_id INTEGER 
testdb(# ); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index “table01_pkey” for table “table01” 
CREATE TABLE 
testdb=#

 

table02を作成します。

testdb=# CREATE TABLE table02 ( 
testdb(#     busho_id INTEGER, 
testdb(#     name VARCHAR(256) NOT NULL 
testdb(# ); 
CREATE TABLE 
testdb=#

 

table01にデータを挿入します。

testdb=# INSERT INTO table01 VALUES(1,’高橋’,1); 
INSERT 0 1 
testdb=# INSERT INTO table01 VALUES(2,’山田’,3); 
INSERT 0 1 
testdb=# INSERT INTO table01 VALUES(3,’鈴木’,2); 
INSERT 0 1 
testdb=# INSERT INTO table01 VALUES(4,’佐藤’,4); 
INSERT 0 1 
testdb=# INSERT INTO table01 VALUES(5,’中村’,1); 
INSERT 0 1 
testdb=# INSERT INTO table01 VALUES(6,’山本’,4); 
INSERT 0 1 
testdb=#

 

table02にデータを挿入します。

testdb=# INSERT INTO table02 VALUES(1,’営業’); 
INSERT 0 1 
testdb=# INSERT INTO table02 VALUES(2,’総務’); 
INSERT 0 1 
testdb=# INSERT INTO table02 VALUES(3,’人事’); 
INSERT 0 1 
testdb=# INSERT INTO table02 VALUES(4,’経理’); 
INSERT 0 1 
testdb=# INSERT INTO table02 VALUES(5,’製造’); 
INSERT 0 1 
testdb=# INSERT INTO table02 VALUES(6,’IT’); 
INSERT 0 1 
testdb=#

 

 

■table01の中身

testdb=# SELECT * FROM table01; 
 no | name | busho_id
—-+——+———-

  1 | 高橋 |        1 
  2 | 山田 |        3 
  3 | 鈴木 |        2 
  4 | 佐藤 |        4 
  5 | 中村 |        1 
  6 | 山本 |        4 
 
testdb=#

 

 

■table02の中身

testdb=# SELECT * FROM table02; 
 busho_id | name
———-+——

        1 | 営業 
        2 | 総務 
        3 | 人事 
        4 | 経理 
        5 | 製造 
        6 | IT 
 
testdb=#

 

 

INNER JOIN の場合

■table01の場合

testdb=# SELECT * FROM table01 INNER JOIN table02 ON table01.busho_id = table02.busho_id; 
 no | name | busho_id | busho_id | name
—-+——+———-+———-+——

  1 | 高橋 |        1 |        1 | 営業 
  2 | 山田 |        3 |        3 | 人事 
  3 | 鈴木 |        2 |        2 | 総務 
  4 | 佐藤 |        4 |        4 | 経理 
  5 | 中村 |        1 |        1 | 営業 
  6 | 山本 |        4 |        4 | 経理 
 
testdb=#

 

 

■table02の場合

testdb=# SELECT * FROM table02 INNER JOIN table01 ON table01.busho_id = table02.busho_id;

 busho_id | name | no | name | busho_id
———-+——+—-+——+———- 
        1 | 営業 |  5 | 中村 |        1 
        1 | 営業 |  1 | 高橋 |        1 
        2 | 総務 |  3 | 鈴木 |        2 
        3 | 人事 |  2 | 山田 |        3 
        4 | 経理 |  6 | 山本 |        4 
        4 | 経理 |  4 | 佐藤 |        4 
 
testdb=#

 

 

 

LEFT OUTER JOIN の場合

■table01の場合

testdb=# SELECT * FROM table01 LEFT OUTER JOIN table02 on table01.busho_id = table02.busho_id; 
 no | name | busho_id | busho_id | name
—-+——+———-+———-+——

  1 | 高橋 |        1 |        1 | 営業 
  2 | 山田 |        3 |        3 | 人事 
  3 | 鈴木 |        2 |        2 | 総務 
  4 | 佐藤 |        4 |        4 | 経理 
  5 | 中村 |        1 |        1 | 営業 
  6 | 山本 |        4 |        4 | 経理 
 
testdb=#

 

 

■table02の場合

testdb=# SELECT * FROM table02 LEFT OUTER JOIN table01 ON table01.busho_id = table02.busho_id; 
 busho_id | name | no | name | busho_id
———-+——+—-+——+———-

        1 | 営業 |  5 | 中村 |        1 
        1 | 営業 |  1 | 高橋 |        1 
        2 | 総務 |  3 | 鈴木 |        2 
        3 | 人事 |  2 | 山田 |        3 
        4 | 経理 |  6 | 山本 |        4 
        4 | 経理 |  4 | 佐藤 |        4 
        5 | 製造 |    |      | 
        6 | IT   |    |      | 
 
testdb=#

 

 

 

RIGHT OUTER JOIN の場合

■table01の場合

testdb=# SELECT * FROM table01 RIGHT OUTER JOIN table02 on table01.busho_id = table02.busho_id; 
 no | name | busho_id | busho_id | name
—-+——+———-+———-+——

  5 | 中村 |        1 |        1 | 営業 
  1 | 高橋 |        1 |        1 | 営業 
  3 | 鈴木 |        2 |        2 | 総務 
  2 | 山田 |        3 |        3 | 人事 
  6 | 山本 |        4 |        4 | 経理 
  4 | 佐藤 |        4 |        4 | 経理 
    |      |          |        5 | 製造 
    |      |          |        6 | IT 
 
testdb=#

 

 

■table02の場合

testdb=# SELECT * FROM table02 RIGHT OUTER JOIN table01 on table01.busho_id = table02.busho_id; 
 busho_id | name | no | name | busho_id
———-+——+—-+——+———-

        1 | 営業 |  1 | 高橋 |        1 
        3 | 人事 |  2 | 山田 |        3 
        2 | 総務 |  3 | 鈴木 |        2 
        4 | 経理 |  4 | 佐藤 |        4 
        1 | 営業 |  5 | 中村 |        1 
        4 | 経理 |  6 | 山本 |        4 
 
testdb=#

 

 

CROSS JOIN の場合

testdb=# SELECT * FROM table01 CROSS JOIN table02; 
 no | name | busho_id | busho_id | name
—-+——+———-+———-+——

  1 | 高橋 |        1 |        1 | 営業 
  1 | 高橋 |        1 |        2 | 総務 
  1 | 高橋 |        1 |        3 | 人事 
  1 | 高橋 |        1 |        4 | 経理 
  1 | 高橋 |        1 |        5 | 製造 
  1 | 高橋 |        1 |        6 | IT 
  2 | 山田 |        3 |        1 | 営業 
  2 | 山田 |        3 |        2 | 総務 
  2 | 山田 |        3 |        3 | 人事 
  2 | 山田 |        3 |        4 | 経理 
  2 | 山田 |        3 |        5 | 製造 
  2 | 山田 |        3 |        6 | IT 
  3 | 鈴木 |        2 |        1 | 営業 
  3 | 鈴木 |        2 |        2 | 総務 
  3 | 鈴木 |        2 |        3 | 人事 
  3 | 鈴木 |        2 |        4 | 経理 
  3 | 鈴木 |        2 |        5 | 製造 
  3 | 鈴木 |        2 |        6 | IT 
  4 | 佐藤 |        4 |        1 | 営業 
  4 | 佐藤 |        4 |        2 | 総務 
  4 | 佐藤 |        4 |        3 | 人事 
  4 | 佐藤 |        4 |        4 | 経理 
  4 | 佐藤 |        4 |        5 | 製造 
  4 | 佐藤 |        4 |        6 | IT 
  5 | 中村 |        1 |        1 | 営業 
  5 | 中村 |        1 |        2 | 総務 
  5 | 中村 |        1 |        3 | 人事 
  5 | 中村 |        1 |        4 | 経理 
  5 | 中村 |        1 |        5 | 製造 
  5 | 中村 |        1 |        6 | IT 
  6 | 山本 |        4 |        1 | 営業 
  6 | 山本 |        4 |        2 | 総務 
  6 | 山本 |        4 |        3 | 人事 
  6 | 山本 |        4 |        4 | 経理 
  6 | 山本 |        4 |        5 | 製造 
  6 | 山本 |        4 |        6 | IT 
 
testdb=#

 

 

 

 

PostgreSQL シリーズ

今まで学習した PostgreSQL の技術をシリーズとしてまとめました。

 

【PostgreSQL】PostgreSQL の特徴と基本【Part.1】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL の特徴と基本【Part.1】
https://go-journey.club/archives/10050
シリーズものとなりますが、現在 OSS RDBMS（オープンソースソフトウェアのリレーショナルデータベース管理システム：PostgreSQL、MySQL など）のスキルを学習しており、その一環として学習したことをまとめます。  (adsbygoogle = window.adsbygoogle || ).push({}); PostgreSQL とはPostgreSQL は開発から 30 年以上経っている古いソフトウェアです。かなり前ですが、PostgreSQL は無償 RDBMS なので各企業で利用しているなと感じたことがありました。しかし、その後 PostgreSQL から MySQL への転換が始まったような気が...

 

【PostgreSQL】【正規化】リレーショナルデータベース（RDMS）の基本【Part.2】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】【正規化】リレーショナルデータベース（RDMS）の基本【Part.2】
https://go-journey.club/archives/10052
データベースを勉強し始める方に取って最初の難関が「正規化」です。 正規化には第１正規化から第５正規化まであります。 第１正規化 第２正規化 第３正規化 第４正規化 第５正規化  (adsbygoogle = window.adsbygoogle || ).push({}); 正規化の目的 データの冗長性をなくすため データの更新時に不整合が起きづらいようにするため 非正規形正規化されていない状態のテーブルを「非正規形」と呼びます。  第１正規化 テーブルの行を一意に特定するための主キーを設定します。 繰り返し現れる列のデータ...

 

【PostgreSQL】PostgreSQL のインストールと初期設定【Part.3】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL のインストールと初期設定【Part.3】
https://go-journey.club/archives/10054
PostgreSQL のインストールと初期設定について解説します。 PostgreSQL のインストールについては以下も参考にしてください。 【PostgreSQL】【CentOS7】yum コマンドで PostgreSQL インストール＆初期設定（ユーザー作成、パスワード設定、データベース作成、権限割り当て）手順  (adsbygoogle = window.adsbygoogle || ).push({}); PostgreSQL の構築手順概要 PostgreSQL のインストール データベースクラスタの作成 サーバの起動 データベースの作成 PostgreSQL のインストールPostgreSQL のインストール...

 

【PostgreSQL】PostgreSQL の標準付属ツールの説明とコマンド手順【Part.4】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL の標準付属ツール(psql、pg_ctl、initdb、COPY、その他)...
https://go-journey.club/archives/10056
今回は PostgreSQL の標準付属ツール（psql、pg_ctl、initdb、\copy、COPY、その他）の使用手順を解説します。   (adsbygoogle = window.adsbygoogle || ).push({}); psql コマンド日々 PostgreSQL データベースを運用しているなら psql コマンドは一番使用するコマンドかもしれません。psql コマンドは OS に対するシェルのような対話型インタフェースです。psql コマンドで様々な処理が可能です。psql コマンドは「スーパーユーザー」、「一般ユーザー」ともに使用できます。  psql コマンドで可能な処理 S...

 

【PostgreSQL】PostgreSQL の設定ファイル（postgresql.conf、pg_hba.conf）の解説【Part.5】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL の設定ファイル（postgresql.conf、pg_hba.conf）の解説...
https://go-journey.club/archives/10058
今回は運用で必須となる PostgreSQL の設定ファイル（postgresql.conf、pg_hba.conf）ファイルについて解説します。  (adsbygoogle = window.adsbygoogle || ).push({}); postgresql.conf ファイルpostgresql.conf ファイルは PostgreSQL の全体の動作を決める設定ファイルです。postgresql.conf ファイルは initdb コマンド実行時に自動的に作成され、データベースクラスタに配置されます。  postgresql.conf ファイルの書式postgresql.conf ファイルの書式は以下です。パラメータ名 = 設定 【例】-bash-4.2...

 

【PostgreSQL】PostgreSQL のバックアップ手順とリストア手順【Part.6】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL のバックアップ手順とリストア手順【Part.6】
https://go-journey.club/archives/10060
今回 PostgreSQL のバックアップおよびリストア手順について解説します。 【PostgreSQL】【シェルスクリプト】PostgreSQLのバックアップスクリプト  (adsbygoogle = window.adsbygoogle || ).push({});  pg_dumpall コマンドpg_dumpall コマンドは論理バックアップを取得します。pg_dumpall コマンドは特定のデータベースを指定してバックアップを取得することはできません。すべてのデータベースが対象としなります。バックアップ形式は「プレーンテキスト形式」のみです。（バイナリ形式でバックアップは取得で...

 

【PostgreSQL】PostgreSQL の運用管理（ユーザー管理、バキューム）【Part.7】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL の運用管理（ユーザー管理、バキューム、インフラ運用な...
https://go-journey.club/archives/10062
今回は PostgreSQL の運用管理（ユーザー管理、バキューム、インフラ運用など）について解説します。  (adsbygoogle = window.adsbygoogle || ).push({}); vacuumdb コマンドデータベースを日々運用していくと、データの更新によって使用されなくなった不要領域が蓄積していきます。vacuumdb コマンドはその不要領域を回収するコマンドです。不要な領域が回収されると、その分の領域が使用できるようになります。 ■デフォルト（オプションなし）-bash-4.2$ vacuumdb-bash-4.2$  -a　←　すべてのデータベースを不要...

 

【PostgreSQL】PostgreSQL の基本的な SQL文 とオブジェクトについて【Part.8】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL の基本的な SQL文 とオブジェクトについて【Part.8】
https://go-journey.club/archives/10064
PostgreSQL の基本的な SQL 文 とオブジェクトについて解説します。  (adsbygoogle = window.adsbygoogle || ).push({});  【SQLのルール】大文字と小文字を区別しない例えば、SELECT 文の場合は「SELECT」と「select」は一緒です。テーブル名やインデックス名も大文字・小文字を区別しません。テーブル名も「TABLE01」と「table01」は同じテーブルを指しています。 文字や日付はシングルクオートで囲む文字や日付はシングルクオートで囲みます。例 2018年11月18日　←　'2018-11-18' ■シングルクオ...

 

【PostgreSQL】PostgreSQL の組み込み関数、ユーザー定義関数、演算子について【Part.9】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL の組み込み関数、ユーザー定義関数、演算子について【Pa...
https://go-journey.club/archives/10066
今回は PostgreSQL の組み込み関数と演算子について解説します。  (adsbygoogle = window.adsbygoogle || ).push({}); システム情報関数関数の最後に括弧（カッコ）が付く関数と、付かない関数があるので注意です。 ■括弧が付く関数 version()　←　PostgreSQL のバージョンを表示します。 current_database()　←　現在接続しているデータベースの情報を表示します。  ■括弧が付かない関数 current_user　←　コマンドを実行したユーザー情報を表示します。 current_timestamp　←　現在の日時を表示します。 cu...

 

【PostgreSQL】PostgreSQL のトランザクションについて【Part.10】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】PostgreSQL のトランザクションについて【Part.10】
https://go-journey.club/archives/10068
今回は PostgreSQL のトランザクションについて解説します。  (adsbygoogle = window.adsbygoogle || ).push({}); トランザクションとは？トランザクションとは、データベースのデータを利用する時（処理する時）に、複数の処理を一連のものとしてまとめる単位のことを言います。例えば、Aさんの銀行の口座から、Bさんの銀行口座にお金（500円）を振り込む場合、 Aさんの銀行口座から500円を引く Bさんの銀行口座に500円を足すをワンセットで処理する必要があります。この処理が片方だけとか、2つの足し引き処理の間に他の処...

 

【PostgreSQL】SQL文【Part.11】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】SQL文【Part.11】
https://go-journey.club/archives/10082
今回は SQL コマンド（SQL 文）について解説します。分かりにくいかもしれませんが、目的別とコマンドの解説を記載しています。  (adsbygoogle = window.adsbygoogle || ).push({}); PostgreSQL の SQL コマンド一覧PostgreSQL の SQL コマンド一覧は以下のメタコマンドで確認できます。postgres=# \h Available help:   ABORT                            CREATE FUNCTION                  DROP TABLE   ALTER AGGREGATE                  CREATE GROUP                     DROP TABLESPACE   ALTER COLLATION           ...

 

【PostgreSQL】テーブル設計（データ型、制約）【Part.12】

AWSに特化したインフラ技術活用ガイド
【PostgreSQL】テーブル設計（データ型、制約）【Part.12】
https://go-journey.club/archives/10090
今回はテーブル設計について解説します。  (adsbygoogle = window.adsbygoogle || ).push({}); テーブルの操作 CREATE TABLE　←　テーブルの作成 DROP TABLE　←　テーブルの削除 ALTER TABLE　←　テーブルの変更  情報スキーマ情報スキーマにはデータベースクラスタに関する情報が格納されています。スキーマのことを「名前空間」と呼ぶこともあります。  システムカタログシステムカタログにはデータベース管理に関する情報が格納されています。  各種制約テーブルを作成するに当たり、以下...

 

 

PostgreSQL データベース
CROSS JOIN INNER JOIN NATURAL JOIN OUTER JOIN PostgreSQL SQL SQLコマンド SQL文 内部結合 外部結合
よかったらシェアしてね！
  • URLをコピーしました！
  • URLをコピーしました！

この記事を書いた人

サイト管理人

関連記事

コメント

コメントする

AlphaOmega Captcha Medica  –  What Do You See?
  ⟲    ➴
 

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。