INNER JOINとOUTER JOINを使いこなす

PostgreSQLにおけるINNER JOINとOUTER JOIN: テーブルの順序は重要か？

結論から言うと、INNER JOINではテーブル順序は重要ではありませんが、OUTER JOINでは重要になります。

INNER JOINは、両方のテーブルに存在する行のみを結合します。テーブル順序は、結合される行の順序に影響を与えますが、最終的な結果には影響を与えません。

例：

-- テーブルt1とt2を結合
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

-- テーブルt2とt1を結合
SELECT * FROM t2 INNER JOIN t1 ON t2.id = t1.id;

上記2つのクエリは、同じ結果を返します。

OUTER JOINは、片方のテーブルまたは両方のテーブルに存在しない行も含めて結合します。テーブル順序は、結合される行の順序と、結果に含まれる行に影響を与えます。

LEFT OUTER JOINは、左側のテーブルのすべての行と、右側のテーブルで一致する行を結合します。右側のテーブルに一致しない行は、NULL値で埋め込まれて結果に含まれます。

-- t1のすべての行と、t2で一致する行を結合
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;

-- t2のすべての行と、t1で一致する行を結合
SELECT * FROM t2 LEFT OUTER JOIN t1 ON t2.id = t1.id;

1つ目のクエリは、t1のすべての行と、t2で一致する行を結合します。t2に一致しないt1の行は、NULL値で埋め込まれて結果に含まれます。

-- t1のすべての行と、t2で一致する行を結合
SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;

-- t2のすべての行と、t1で一致する行を結合
SELECT * FROM t2 RIGHT OUTER JOIN t1 ON t2.id = t1.id;

-- t1とt2のすべての行を結合
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

• INNER JOIN: テーブル順序は結果に影響を与えない。
• LEFT OUTER JOIN: 左側のテーブルのすべての行と、右側のテーブルで一致する行を結合。
• FULL OUTER JOIN: 両方のテーブルのすべての行を結合。

PostgreSQLにおけるINNER JOINとOUTER JOINのサンプルコード

テーブル構成

• users: ユーザー情報
  • id: ユーザーID (主キー)
  • name: ユーザー名
• orders: 注文情報
  • product: 商品名

INNER JOIN

-- ユーザーと注文情報を結合
SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

結果

| name | product |
|---|---|
| John Doe | Book |
| Jane Doe | Laptop |

LEFT OUTER JOIN

-- ユーザーと注文情報を結合 (ユーザーすべての行を取得)
SELECT u.name, o.product
FROM users u
LEFT OUTER JOIN orders o ON u.id = o.user_id;

| name | product |
|---|---|
| John Doe | Book |
| Jane Doe | Laptop |
| Mary Sue | NULL |

-- ユーザーと注文情報を結合 (注文すべての行を取得)
SELECT u.name, o.product
FROM users u
RIGHT OUTER JOIN orders o ON u.id = o.user_id;

| name | product |
|---|---|
| John Doe | Book |
| Jane Doe | Laptop |
| NULL | Phone |

-- ユーザーと注文情報を結合 (ユーザーと注文すべての行を取得)
SELECT u.name, o.product
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

| name | product |
|---|---|
| John Doe | Book |
| Jane Doe | Laptop |
| Mary Sue | NULL |
| NULL | Phone |

解説

• 上記の例では、INNER JOINはユーザーと注文情報が一致する行のみを結合しています。
• LEFT OUTER JOINは、ユーザーすべての行と、注文で一致する行を結合しています。ユーザーに注文がない場合、product列はNULLになります。

これらのサンプルコードを参考に、INNER JOINとOUTER JOINの違いを理解し、目的に合致したクエリを作成してください。

PostgreSQLにおけるINNER JOINとOUTER JOINのその他の方法

テーブルの列を結合する

-- ユーザー名と注文商品を結合
SELECT u.name || ' ordered ' || o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

| name |
|---|---|
| John Doe ordered Book |
| Jane Doe ordered Laptop |

集計関数を使用する

-- ユーザーあたりの注文数を集計
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT OUTER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

| name | order_count |
|---|---|
| John Doe | 1 |
| Jane Doe | 1 |
| Mary Sue | 0 |

サブクエリを使用する

-- 注文商品が "Book" であるユーザーの名前を取得
SELECT name
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE product = 'Book'
);

| name |
|---|---|
| John Doe |

CASE式を使用する

-- ユーザーの注文状況を表示
SELECT u.name,
  CASE WHEN o.product IS NULL THEN 'No orders'
    ELSE o.product
  END AS order_status
FROM users u
LEFT OUTER JOIN orders o ON u.id = o.user_id;

| name | order_status |
|---|---|
| John Doe | Book |
| Jane Doe | Laptop |
| Mary Sue | No orders |

これらの方法は、INNER JOINとOUTER JOINを組み合わせて使用することもできます。

INNER JOINとOUTER JOINは、さまざまな方法でテーブルを結合したり、データを取得したりするために使用できます。上記の方法を参考に、目的に合致したクエリを作成してください。