Zenn
Open5

【達人に学ぶSQL徹底指南書メモ】3. 自己結合の使い方

SQL
DB
churuchuruchuruchuru

3.1 重複順列

Products

name price
りんご 100
みかん 50
バナナ 80 
SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1
  CROSS JOIN Products P2;

結果

name_1 name_2
りんご りんご
りんご みかん
りんご バナナ
みかん りんご
みかん みかん
みかん バナナ
バナナ りんご
バナナ みかん
バナナ バナナ

以下のようにも書けるが、古い書き方なので非推奨。

SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1,
  Products P2;
churuchuruchuruchuru

3.2 順列

SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1
  INNER JOIN Products P2
    ON P1.name <> P2.name;

結果

name_1 name_2
りんご みかん
りんご バナナ
みかん りんご
みかん バナナ
バナナ りんご
バナナ みかん

古い書き方では以下のようにも書ける。
WHERE 句を書き忘れるとクロス結合になってしまうため非推奨。
INNER JOIN を使えば、ON 句を書き忘れるとエラーになる。

SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1,
  Products P2
WHERE
  P1.name <> P2.name;
churuchuruchuruchuru

3.3 組み合わせ

SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1
  INNER JOIN Products P2
    ON P1.name > P2.name;

結果

name_1 name_2
りんご みかん
バナナ みかん
バナナ りんご

3 列に拡張すると

SELECT
  P1.name AS name_1,
  P2.name AS name_2,
  P3.name AS name_3
FROM
  Products P1
    INNER JOIN Products P2
      ON P1.name > P2.name
        INNER JOIN Products P3
          ON P2.name > P3.name;
name_1 name_2 name_3
りんご みかん バナナ
churuchuruchuruchuru

3.4 重複組合せ

SELECT
  P1.name AS name_1,
  P2.name AS name_2
FROM
  Products P1
  INNER JOIN Products P2
    ON P1.name >= P2.name;

結果

name_1 name_2
バナナ みかん
バナナ りんご
バナナ バナナ
りんご みかん
りんご りんご
みかん みかん
churuchuruchuruchuru

3.5 重複行を削除する

Products

name price
りんご 50
みかん 100
みかん 100
みかん 100
バナナ 80

結果

name price
バナナ 80
みかん 100
りんご 50

3.5.1 実装依存のレコード ID を使う

Oracle の rowid を使った例

極値関数を利用した場合
DELETE
FROM
  Products P1
WHERE
  rowid < (
    SELECT MAX(P2.rowid)
    FROM Products P2
    WHERE P1.name = P2.name
      AND P1.price = P2.price
  );
非等値結合を利用した場合
DELETE
FROM
  Products P1
WHERE
  EXISTS(
    SELECT *
    FROM Products P2
    WHERE P1.name = P2.name
      AND P1.price = P2.price
      AND P1.rowid < P2.rowid
  );

3.5.2 実装依存の機能を使わない

(name, price) をキーとするパーティションを作って連番を振る。

CREATE TABLE Products_NoRedundant AS
SELECT
  ROW_NUMBER()
    OVER(
      PARTITION BY
        name,
        price
        ORDER BY
          name
    ) AS row_num,
  name,
  price
FROM
  Products;

Products_NoRedundant

row_num name price
1 バナナ 80
1 みかん 100
2 みかん 100
3 みかん 100
1 りんご 50

連番が 1 以外のレコードを削除する。

DELETE
FROM
  Products_NoRedundant
WHERE
  row_num > 1;