外部結合について

外部結合に関する質問です。

表A 　 　　　　 　|　　表B
ID　　 名前　　　|　　ID　　 所持品
1　　　田中　　　|　　1　　　りんご
2　　　佐藤　　　|　　1　　　バナナ
3　　　鈴木　　　|　　3　　　バナナ

このように「誰が、何を持っているか」を表した表AとBがあるとき、
以下の条件でデータを取得したいと考えております。

・ 所持品がある人は、名前と所持品の組み合わせを表示
・ 所持品がない人は、名前だけを表示
・ りんごをもっていない人は、名前と所持品の組み合わせに
　加え名前だけの結果も表示
・ UNIONは使いたくない

( 欲しい結果 )
　名前　　　　所持品
　田中　　　　りんご
　田中　　　　バナナ
　佐藤　　　　（空）　　　← 所持品なし
　鈴木　　　　バナナ
　鈴木　　　　（空）　　　← りんごなし

SELECT A.名前, B.所持品 FROM A, B WHERE A.ID = B.ID(+)
では『鈴木 （ 空 ）』 が取得できません。。。
UNIONを使えばできたのですが、都合上UNIONを使わず
上記のような結果を取得したいと考えています。

どなたかお知恵をお貸しいただけませんでしょうか。
よろしくお願いします。

No.5 ベストアンサー 回答者： dda167 回答日時： 2011/04/17 08:26

>件数が多いとちょっと重くなりそうな気がするのですが

『鈴木（空）』を取得するのが表示上の問題だとすると、
SQLとして本質的なことではないでしょう。
（人間様には必要でも、DBではあずかり知らぬこと）
そんな場合、DBには
SELECT A.名前, B.所持品 FROM A, B WHERE A.ID = B.ID(+)
だけ投げておいて、『鈴木（空）』はフロントエンド側で
何とかする……というのも一つの方法だと思います。

今回は、面白そうなSQLだったので参加しました（笑）。

この回答へのお礼

お礼が遅くなって申し訳ありませんでした。
dda167さんの

>SQLとして本質的なことではないでしょう

という尤もなご指摘を受けて考え直しました。
色々と検討したのですが、今回はUNIONを使用した上で
プログラムの方をどうにかすることで解決いたしました。

的確なご指摘をいただいた点でベストアンサーに
選ばせていただきました。ありがとうございました！

ShimoHayhaさんの回答もSQLの知識の上で大変参考になりました。
結果的にベストアンサーにお選びできませんでしたが、
とても勉強になりました。ありがとうございました！

お礼日時：2011/04/23 14:18

No.4 回答者： dda167 回答日時： 2011/04/17 07:13

>コストを気にするとやはりUNIONを使う方がいいのでしょうか。

。。

コストを気にするなら、UNION ALLを使ったほうが……
（重複問題がなければの話ですけど）

SQLは、同じ結果を得るのに、いろいろな書き方があるじゃないですか。
パフォーマンスやメンテナンスなどを考慮して最終的に決定するでしょうが、
その選択肢を自ら削る必要もないのでは……と思っただけです。

10gだとこんな書き方もありますね。

SELECT DISTINCT T.名前, T.所持品 FROM
(SELECT A.名前, B.所持品 FROM A LEFT OUTER JOIN B ON A.ID = B.ID) T
PARTITION BY (名前) RIGHT OUTER JOIN
(SELECT DISTINCT 所持品 FROM B) M
ON T.所持品 = M.所持品

No.3 回答者： ShimoHayha 回答日時： 2011/04/16 17:53

No.2 です。

No.2 は明らかに間違ってました。
こんな感じですかね・・・（またまた未検証ですが）

SELECT COALESCE(S.名前, R.名前), S.所持品
FROM
(
SELECT A.ID ID, A.名前 名前, B.所持品 所持品
FROM A
LEFT OUTER JOIN B ON A.ID = B.ID
)
S
FULL OUTER JOIN
(
SELECT A.ID ID, A.名前 名前
FROM A
LEFT INNER JOIN B ON A.ID = B.ID AND B.所持品 != 'りんご'
)
R
ON S.ID = R.ID

この回答へのお礼

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

今検証できる環境ではないので確認はまだなのですが、
COALESCという関数は知らなかったです。
検証してみて、またご報告させていただきます。

取り急ぎ御礼まで。ありがとうございます。

お礼日時：2011/04/17 02:24

No.2 回答者： ShimoHayha 回答日時： 2011/04/16 16:04

え～っと、未検証ですが、こういうことでしょうかね？

SELECT
A.名前, B.所持品
FROM
A
LEFT OUTER JOIN B ON A.ID = B.ID
LEFT OUTER JOIN
(
SELECT A.ID, B.所持品
FROM A
LEFT OUTER JOIN B
ON A.ID = B.ID AND B.所持品 = 'りんご'
WHERE B.所持品 IS NULL
) R
ON
A.ID = R.ID

No.1 回答者： dda167 回答日時： 2011/04/16 15:54

「UNIONは使いたくない」理由は何ですか？

SELECT DISTINCT AX.名前, B.所持品 FROM (
SELECT DISTINCT A.ID, A.名前, B.所持品 FROM A, B
) AX, B
WHERE AX.ID = B.ID(+) AND AX.所持品 = B.所持品(+);

この回答へのお礼

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

SQLを動的に作成する箇所を含むプログラムを書いているのですが、
UNIONを使わないで済めば実装が簡単になりそうなので
そうした方法があれば、と思いまして。。。

今は検証ができないのですが、このSQLなら確かに取れそうですね。
件数が多いとちょっと重くなりそうな気がするのですが、
コストを気にするとやはりUNIONを使う方がいいのでしょうか。。。

その辺り確認してみたいと思います。ありがとうございます。

お礼日時：2011/04/17 02:12