第3章の動作確認: 3.3 問い合わせ

3.3 問い合わせ

以下を確認します。

単純なSELECT文を使う
グループ化を含むSELECT文を使う
複数の表を対象としたSELECT文を使う

準備

最初に前節で作成したprod表、customer表、orders表を削除して、新たに作成し直してデータを格納します。

[postgres@host1 ~]$ psql ossdb
psql (13.14)
"help"でヘルプを表示します。

ossdb=# DROP TABLE IF EXISTS orders, prod, customer;
NOTICE:  テーブル"orders"は存在しません、スキップします
DROP TABLE
ossdb=# CREATE TABLE prod (
    prod_id INTEGER NOT NULL,
    prod_name TEXT NOT NULL,
    price INTEGER DEFAULT 0 CHECK(price >= 0),
    PRIMARY KEY (prod_id));
CREATE TABLE
ossdb=# CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL);
CREATE TABLE
ossdb=# CREATE TABLE orders (
    order_id INTEGER UNIQUE,
    order_date DATE,
    customer_id INTEGER REFERENCES customer(customer_id),
    prod_id INTEGER,
    qty INTEGER,
    FOREIGN KEY (prod_id) REFERENCES prod(prod_id));
CREATE TABLE
ossdb=# INSERT INTO prod VALUES
    (1, 'みかん', 50), (2, 'りんご', 70), (3, 'メロン', 100), (4, 'バナナ', 30);
INSERT 0 4
ossdb=# INSERT INTO customer VALUES
    (1, '佐藤商事'), (2, '鈴木物産'), (3, '高橋商店');
INSERT 0 3
ossdb=# INSERT INTO orders VALUES
    (1, '2024-04-01', 1, 1, 10),
    (2, '2024-04-01', 2, 2, 5),
    (3, '2024-04-01', 3, 3, 8),
    (4, '2024-04-02', 2, 1, 3),
    (5, '2024-04-02', 3, 2, 4);
INSERT 0 5
ossdb=# SELECT * FROM prod;
 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん    |    50
       2 | りんご    |    70
       3 | メロン    |   100
       4 | バナナ    |    30
(4 行)

ossdb=# SELECT * FROM customer;
 customer_id | customer_name
-------------+---------------
           1 | 佐藤商事
           2 | 鈴木物産
           3 | 高橋商店
(3 行)

ossdb=# SELECT * FROM orders;
 order_id | order_date | customer_id | prod_id | qty
----------+------------+-------------+---------+-----
        1 | 2024-04-01 |           1 |       1 |  10
        2 | 2024-04-01 |           2 |       2 |   5
        3 | 2024-04-01 |           3 |       3 |   8
        4 | 2024-04-02 |           2 |       1 |   3
        5 | 2024-04-02 |           3 |       2 |   4
(5 行)

ossdb=#

これらの表は、本節以降このままの状態で使用します。

単純なSELECT文

[postgres@host1 ~]$ossdb=# SELECT 1+2*3; --SELECT文(1)
 ?column?
----------
        7
(1 行)

ossdb=# SELECT COUNT(*) FROM prod; --SELECT文(2)
 count
-------
     4
(1 行)

ossdb=# SELECT * FROM prod WHERE prod_id = 1; --SELECT文(3)
 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん    |    50
(1 行)

ossdb=# SELECT prod_name, price --SELECT文(4)
    FROM prod
    WHERE price < 70;
 prod_name | price
-----------+-------
 みかん    |    50
 バナナ    |    30
(2 行)

ossdb=# SELECT prod_id FROM orders; --SELECT文(5)
 prod_id
---------
       1
       2
       3
       1
       2
(5 行)

ossdb=# SELECT DISTINCT prod_id FROM orders; --SELECT文(6)
 prod_id
---------
       3
       2
       1
(3 行)

ossdb=# SELECT order_id, prod_id, qty --SELECT文(7)
    FROM orders
    ORDER BY qty DESC;
 order_id | prod_id | qty
----------+---------+-----
        1 |       1 |  10
        3 |       3 |   8
        2 |       2 |   5
        5 |       2 |   4
        4 |       1 |   3
(5 行)

ossdb=# SELECT order_id, prod_id, qty --SELECT文(8)
    FROM orders
    ORDER BY qty DESC
    LIMIT 1
    OFFSET 2;
 order_id | prod_id | qty
----------+---------+-----
        2 |       2 |   5
(1 行)

ossdb=#

SELECT文(1)は、FROM句がなしで四則演算を行っています。SELECT文(2)は、prod表の行数を出力しています。SELECT文(3)(4)は、WHERE句の条件で行を選択しています。
SELECT文(5)(6)は、DISTINCTにより重複が除去されることを確認しています。なお、出力の順序は必ずしも行が格納されている順序にはならないことに注意してください。SELECT文(7)(8)は、ORDER BY句、LIMIT句、OFFSET句の動作を確認しています。ORDER BY句を使うことにより、出力の順序をコントロールしています。

グループ化を含むSELECT文

ossdb=# SELECT prod_id, COUNT(*) --SELECT文(1)
    FROM orders
    GROUP BY prod_id;
 prod_id | count
---------+-------
       3 |     1
       2 |     2
       1 |     2
(3 行)

ossdb=# SELECT prod_id, order_id, COUNT(*) --エラー
    FROM orders
    GROUP BY prod_id;
ERROR:  列"orders.order_id"はGROUP BY句で指定するか、集約関数内で使用しなければなりません
行 1: SELECT prod_id, order_id, COUNT(*)
                      ^
ossdb=# SELECT prod_id, COUNT(*) --SELECT文(2)
    FROM orders
    GROUP BY prod_id
    HAVING COUNT(*) = 2;
 prod_id | count
---------+-------
       2 |     2
       1 |     2
(2 行)

ossdb=# SELECT prod_id, COUNT(*) --SELECT文(3)
    FROM orders
    WHERE customer_id > 1
    GROUP BY prod_id;
 prod_id | count
---------+-------
       3 |     1
       2 |     2
       1 |     1
(3 行)

ossdb=# SELECT order_date, COUNT(*), SUM(qty) --SELECT文(4)
    FROM orders
    GROUP BY order_date
    ORDER BY order_date;
 order_date | count | sum
------------+-------+-----
 2024-04-01 |     3 |  23
 2024-04-02 |     2 |   7
(2 行)

ossdb=#

SELECT文(1)は、orders表をprod_id列でグループ化して、グループごとの行数を出力しています。次に、GROUP BY句で指定していないorder_id列は出力できないことを確認しています。
SELECT文(2)(3)(4)は、GROUP BY句とHAVING句、WHERE句、ORDER BY句を併用したときの動作を確認しています。グループ化は、WHERE句で選択された行の集合に対して行われることに注意してください。

複数の表を対象とするSELECT文

最初にprod表と同じ列構成で異なる行が格納されたprod2表を作成した後、集合演算を行うSELECT文を実行します。

ossdb=# CREATE TABLE prod2 ( --prod2表の作成
    prod_id INTEGER NOT NULL,
    prod_name TEXT NOT NULL,
    price INTEGER DEFAULT 0 CHECK(price >= 0),
    PRIMARY KEY (prod_id));
CREATE TABLE
ossdb=# INSERT INTO prod2 VALUES
    (1, 'みかん', 50), (3, 'メロン', 100), (5, 'ぶどう', 50);
INSERT 0 3
ossdb=# SELECT * FROM prod2;
 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん    |    50
       3 | メロン    |   100
       5 | ぶどう    |    50
(3 行)

ossdb=# SELECT prod_id, prod_name, price FROM prod --SELECT文(1)
    UNION
    SELECT prod_id, prod_name, price FROM prod2;
 prod_id | prod_name | price
---------+-----------+-------
       4 | バナナ    |    30
       5 | ぶどう    |    50
       1 | みかん    |    50
       3 | メロン    |   100
       2 | りんご    |    70
(5 行)

ossdb=# SELECT prod_id, prod_name FROM prod --SELECT文(2)
    EXCEPT
    SELECT prod_id, prod_name FROM prod2;
 prod_id | prod_name
---------+-----------
       4 | バナナ
       2 | りんご
(2 行)

ossdb=# SELECT prod_id, prod_name FROM prod --SELECT文(3)
    INTERSECT
    SELECT prod_id, prod_name FROM prod2;
 prod_id | prod_name
---------+-----------
       3 | メロン
       1 | みかん
(2 行)

ossdb=# SELECT prod_id, prod_name FROM prod --SELECT文(4)
    EXCEPT SELECT prod_id, prod_name FROM prod2
        WHERE prod_id <> 3
    ORDER BY prod_id;
 prod_id | prod_name
---------+-----------
       2 | りんご
       3 | メロン
       4 | バナナ
(3 行)

ossdb=#

SELECT文(1)は和集合、SELECT文(2)は差集合、SELECT文(3)は共通集合を出力しています。SELECT文(4)は、EXCEPT、WHERE、ORDER BYの組み合わせです。WHERE句はprod2表の検索条件であり、ORDER BY句は差集合をソートしていることに注意してください。

次に、同じ表を使って結合演算を実行します。

ossdb=# SELECT order_id, order_date, customer_id, prod2.prod_id, prod_name, qty --SELECT文(1)
    FROM orders
        JOIN prod2 ON orders.prod_id = prod2.prod_id;
 order_id | order_date | customer_id | prod_id | prod_name | qty
----------+------------+-------------+---------+-----------+-----
        1 | 2024-04-01 |           1 |       1 | みかん    |  10
        3 | 2024-04-01 |           3 |       3 | メロン    |   8
        4 | 2024-04-02 |           2 |       1 | みかん    |   3
(3 行)

ossdb=# SELECT order_id, order_date, customer_id, prod2.prod_id, prod_name, qty --SELECT文(2)
    FROM orders, prod2
    WHERE orders.prod_id = prod2.prod_id;
 order_id | order_date | customer_id | prod_id | prod_name | qty
----------+------------+-------------+---------+-----------+-----
        1 | 2024-04-01 |           1 |       1 | みかん    |  10
        3 | 2024-04-01 |           3 |       3 | メロン    |   8
        4 | 2024-04-02 |           2 |       1 | みかん    |   3
(3 行)

ossdb=# SELECT order_id, order_date, customer_id, p2.prod_id, prod_name, qty --SELECT文(3)
    FROM orders AS o
        LEFT JOIN prod2 AS p2 ON o.prod_id = p2.prod_id;
 order_id | order_date | customer_id | prod_id | prod_name | qty
----------+------------+-------------+---------+-----------+-----
        1 | 2024-04-01 |           1 |       1 | みかん    |  10
        2 | 2024-04-01 |           2 |         |           |   5
        3 | 2024-04-01 |           3 |       3 | メロン    |   8
        4 | 2024-04-02 |           2 |       1 | みかん    |   3
        5 | 2024-04-02 |           3 |         |           |   4
(5 行)

ossdb=# SELECT order_id, order_date, customer_id, p2.prod_id, prod_name, qty --SELECT文(4)
    FROM orders AS o
        RIGHT JOIN prod2 AS p2 ON o.prod_id = p2.prod_id;
 order_id | order_date | customer_id | prod_id | prod_name | qty
----------+------------+-------------+---------+-----------+-----
        1 | 2024-04-01 |           1 |       1 | みかん    |  10
        3 | 2024-04-01 |           3 |       3 | メロン    |   8
        4 | 2024-04-02 |           2 |       1 | みかん    |   3
          |            |             |       5 | ぶどう    |
(4 行)

ossdb=# SELECT order_id, order_date, customer_id, p2.prod_id, prod_name, qty --SELECT文(5)
    FROM orders AS o
        FULL JOIN prod2 AS p2 ON o.prod_id = p2.prod_id;
 order_id | order_date | customer_id | prod_id | prod_name | qty
----------+------------+-------------+---------+-----------+-----
        1 | 2024-04-01 |           1 |       1 | みかん    |  10
        2 | 2024-04-01 |           2 |         |           |   5
        3 | 2024-04-01 |           3 |       3 | メロン    |   8
        4 | 2024-04-02 |           2 |       1 | みかん    |   3
        5 | 2024-04-02 |           3 |         |           |   4
          |            |             |       5 | ぶどう    |
(6 行)

ossdb=# SELECT order_id, order_date, customer_name, prod_name, qty --SELECT文(6)
    FROM orders AS o JOIN prod AS p1 ON o.prod_id = p1.prod_id
        JOIN customer AS c ON o.customer_id = c.customer_id;
 order_id | order_date | customer_name | prod_name | qty
----------+------------+---------------+-----------+-----
        1 | 2024-04-01 | 佐藤商事      | みかん    |  10
        2 | 2024-04-01 | 鈴木物産      | りんご    |   5
        3 | 2024-04-01 | 高橋商店      | メロン    |   8
        4 | 2024-04-02 | 鈴木物産      | みかん    |   3
        5 | 2024-04-02 | 高橋商店      | りんご    |   4
(5 行)

ossdb=# SELECT p1.prod_name --SELECT文(7)
    FROM prod AS p1
        JOIN prod2 AS p2 ON p1.price > p2.price
    WHERE p2.prod_name = 'みかん';
 prod_name
-----------
 りんご
 メロン
(2 行)

ossdb=# SELECT * --SELECT文(8)
    FROM prod AS p1, prod2 AS p2;
 prod_id | prod_name | price | prod_id | prod_name | price
---------+-----------+-------+---------+-----------+-------
       1 | みかん    |    50 |       1 | みかん    |    50
       1 | みかん    |    50 |       3 | メロン    |   100
       1 | みかん    |    50 |       5 | ぶどう    |    50
       2 | りんご    |    70 |       1 | みかん    |    50
       2 | りんご    |    70 |       3 | メロン    |   100
       2 | りんご    |    70 |       5 | ぶどう    |    50
       3 | メロン    |   100 |       1 | みかん    |    50
       3 | メロン    |   100 |       3 | メロン    |   100
       3 | メロン    |   100 |       5 | ぶどう    |    50
       4 | バナナ    |    30 |       1 | みかん    |    50
       4 | バナナ    |    30 |       3 | メロン    |   100
       4 | バナナ    |    30 |       5 | ぶどう    |    50
(12 行)

ossdb=# SELECT p1.prod_name --SELECT文(9)
    FROM prod AS p1, prod2 AS p2
    WHERE p1.price > p2.price AND p2.prod_name = 'みかん';
 prod_name
-----------
 りんご
 メロン
(2 行)

ossdb=#

SELECT文(1)は、orders表とprod2表をprod_id列で内部結合しています。SELECT文(2)は、クロス結合とWHERE句を使ってSELECT文(1)と同じことを行っています。SELECT文(3)(4)(5)は、それぞれ左外部結合、右外部結合、フル外部結合を行っています。SELECT文(6)は、orders表、prod表、customer表の3つを結合しています。
SELECT文(7)は、prod表とprod2表をprice列で内部結合して、「みかん」よりも価格が高い商品を出力しています。結合条件が不等号であることと、2つの表の列名が同じであるため別名を使って区別していることに注意してください。不等号による結合が分かりにくい場合は、SELECT文(8)のようにprod表とprod2表をクロス結合して、さらにSELECT文(9)のようにWHERE句で条件を追加しても同じことができます。