SQLの自己結合について

Question

SQLの自己結合について質問です。

上のようなテーブルに対して、次のようなSQLを実行した時、

このような結果を得ることができます。

SQL内のサブクエリの部分が理解できれば、簡単にわかるのですが、サブクエリの部分がイマイチどのように実行されているか分かりません。
色々調べてみたのですが、よく分かりませんでした。
二つの組織図テーブルをスキャンさせているわけでもなさそうですし。。

お分かりの方、少し解説していただけないでしょうか？

Answer 1

自己結合と相関副問合わせが混在して分からなくなってる感があるので、
一つずつ整理すると良いかもしれません。

#自己結合
先ず自己結合だけに絞ると、maisumakunさんの仰るとおりです。
テーブル別名を付ける(ここでは部下・上司)ことで実態は１つテーブルでも、
それぞれが別のテーブルとして振舞わせることができます。

直感的に分かりやすいようテーブル構成をもっと単純化してみます。

• 社員マスタ

社員コード	社員名	上司コード
101	キラ・ヤマト	NULL
201	アスラン・ザラ	NULL
301	シン・アスカ	201

上記のテーブルで「シン・アスカ」さんの上司名を知りたい・・・、
といった場合に自己結合が用いられます。
SQLにすると以下のような感じ。

SELECT
    上司.社員名  -- あんたって人はー！！
FROM
    社員マスタ 上司 
    INNER JOIN 社員マスタ 社員 
      ON 上司.社員コード = 社員.上司コード 
WHERE
    社員.社員名 = 'シン・アスカ'

まぁつまるところ別名を付けると、
別テーブルとして扱えるから結合でもサブクエリでも書けるよねってお話ですね。

#相関副問合わせ
相関副問合せと書くと難しそうに聞こえますが、
要するにメイン側のクエリ(今回だと一番外側のクエリ)の結果1行に対して動作するクエリのことを指します。
今回の例だとNOT EXISTS句内のクエリは、
上司と別名を付けられた組織図のレコード1行ごとに動作するものとなります。

例えばメイン側のクエリで取り出したレコードが「アダム」の場合、
NOT EXISTS内のクエリは下記のように評価されます。

SELECT
    *
FROM
    組織図 部下 
WHERE
    部下.左端 > 1  -- (アダムの左端の値) 
AND 部下.左端 < 14 -- (アダムの右端の値)

今回はNOT EXISTS句なので、
上記クエリの検索結果がO件の場合のみ「TRUE」を返します。
(※つまりメイン側のクエリのWHERE条件で抽出対象になる)

これを残りのレコード分(イブ～ノアのデータ）反復処理されます。

これと反対にEXISTS句というのもありますが、
これは反対にレコードが1件でもあれば「TRUE」を返し、
0件の場合は「FALSE」と評価されます。

ちなみに相関副問合せは今回のように、
メイン側クエリ抽出データ1件ごとに動作し、
それ単体では成立し得ないサブクエリのことを、
特に他のサブクエリと区別して「相関副問合せ」とか「相関サブクエリ」とか呼ばれたりします。
(まぁ結局サブクエリの仲間ではあるんですがね・・・)

Comments

[退会済みユーザー]

回答ありがとうございます。

Answer 2

副照会の条件

上司の立場で部下がいるかどうか確認します。
「上司.左端 < 部下.左端 < 上司.右端」となる部下がいるかどうか。
NOT EXISTSなので部下がいない上司が選択されます。

アダム 1 14 が上司の場合の部下
イブ 2 3
セト 4 13
カイン 5 8
ヨブ 6 7
アベル 9 10
ノア 11 12

イブ 2 3 が上司の場合の部下

セト 4 13 が上司の場合の部下
カイン 5 8
ヨブ 6 7
アベル 9 10
ノア 11 12

カイン 5 8 が上司の場合の部下
ヨブ 6 7

ヨブ 6 7 が上司の場合の部下

アベル 9 10 が上司の場合の部下

ノア 11 12 が上司の場合の部下

結論 ＜＜部下がいない上司は？＞＞

イブ 2 3
ヨブ 6 7
アベル 9 10
ノア 11 12

###結合ですか？（追記）
これは自己結合ですか？ただの副問い合わせではないでしょうか？
次のように明に記述する自己結合（部下のいる上司）なら納得しますが。

select distinct 上司.社員 from 組織図 上司 
join 組織図 部下 on (上司.左端 < 部下.左端 and 部下.左端 < 上司.右端);

Comments

[退会済みユーザー]

具体例を示してくださり、ありがとうございました。
非常に分かりやすかったです。

Answer 3

もしもDBの構造自体（入れ子集合モデル）が不明瞭であれば
http://www.geocities.jp/mickindex/database/db_tree_ns.html
をご覧いただくとよいかも知れません。

Comments

[退会済みユーザー]

回答ありがとうございました。

Answer 4

以下を実行するとイメージがわかりやすいかもしれません

create table sosikizu(no varchar(20),l int ,r int);
insert into sosikizu values('アダム',1,14),('イブ',2,3),('セト',4,13),('カイン',5,8),('ヨブ',6,7),('アベル',9,10),('ノア',11,12);

実行

select * from sosikizu as t1
inner join sosikizu as t2 on t2.l>t1.l and t2.l<t1.r
order by t1.l,t2.l;

上記実行すると上司と部下の関係図がわかります。
これがNOT EXISTSなので、組織図から上司とっぱらうと部下のみが表示されます

Comments

[退会済みユーザー]

回答ありがとうございました。

Answer 5

二つの組織図テーブルをスキャンさせているわけでもなさそうですし。。

物理的な挙動はともかく、SQLの処理の上では、あたかも「上司」と「部下」という2つのテーブルがあるかのように動きます。実物が1枚しかないことは忘れて、「二つの組織図テーブルをスキャンさせている」と考えてください。

Comments

[退会済みユーザー]

回答ありがとうございました。