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

表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を使わず
上記のような結果を取得したいと考えています。

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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

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



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

今回は、面白そうなSQLだったので参加しました(笑)。
    • good
    • 0
この回答へのお礼

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

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

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

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

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

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

>コストを気にするとやはり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.所持品
    • good
    • 0

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
    • good
    • 0
この回答へのお礼

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

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

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

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

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




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
    • good
    • 0

「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.所持品(+);
    • good
    • 0
この回答へのお礼

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

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

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

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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q外部結合と等価結合のパフォーマンスの違いについて(ビューの場合)

Oracle10gでのSQL文の違いについて教えて下さい。

前回の質問は、ストアドプロシージャに記述
されていて、バッチとして動かしています。
と書きましたが、ビューの場合のパフォーマンスの違いは
どうなるのでしょうか?ビューの場合も同じような現象です。
下記の2つのSQL文は外部結合ありと外部結合なしの違いだけで、
他は変わりありません。
外部結合ありのほうは
結果がすぐに返されるのですが、外部結合なしのほうは
結果が返ってこない、あるいはかなり時間がかかるという
現象が起きています。
SQL文は簡略して記述していますが、SELECT句には、
TO_CHAR()やSUM(CASE WHEN ...THEN ...ELSE...)が使用してあり
少し重くなる処理も含まれています。
この2つのSQL文でパフォーマンスに影響している原因は
何なんでしょうか?オプティマイザとか実行計画とかの
説明を読んだのですが、いまいちよく解りません。。
自分では中級者以下だと思っていますので、わかりやすく
説明して頂けたら助かります。宜しくお願い致します。

(外部結合ありのSQL)
SELECT
 a.項目1,
 a.項目2,
 a.項目3,
 a.項目4,
 a.項目5
FROM
 TBL_A a,
 TBL_B b
WHERE
 a.項目1 = b.項目1(+) AND
 a.項目2 = b.項目2(+) AND
 a.項目3 = b.項目3(+) AND
 a.項目4 = b.項目4(+) AND
GROUP BY
 a.項目1,
 a.項目2,
 a.項目3,
 a.項目4,
 a.項目5

(外部結合なしのSQL)
SELECT
 a.項目1,
 a.項目2,
 a.項目3,
 a.項目4,
 a.項目5
FROM
 TBL_A a,
 TBL_B b
WHERE
 a.項目1 = b.項目1 AND
 a.項目2 = b.項目2 AND
 a.項目3 = b.項目3 AND
 a.項目4 = b.項目4 AND
GROUP BY
 a.項目1,
 a.項目2,
 a.項目3,
 a.項目4,
 a.項目5

Oracle10gでのSQL文の違いについて教えて下さい。

前回の質問は、ストアドプロシージャに記述
されていて、バッチとして動かしています。
と書きましたが、ビューの場合のパフォーマンスの違いは
どうなるのでしょうか?ビューの場合も同じような現象です。
下記の2つのSQL文は外部結合ありと外部結合なしの違いだけで、
他は変わりありません。
外部結合ありのほうは
結果がすぐに返されるのですが、外部結合なしのほうは
結果が返ってこない、あるいはかなり時間がかかるという
現象が起きています...続きを読む

Aベストアンサー

オラクルのオプティマイザは、テーブルの検索の方法を決めますが、外部結合と内部結合で
立案される実行計画に違いがあり、内部結合の方が合理的な検索方法を立案しているので
結果的に、内部結合の方が速いという話なんですが・・

実行計画を見なければ、判りませんが、
・外部結合はbに対して全表検索を選択した。
・内部結合では、bを索引検索を選択した。
・bの検索量が、bの格納件数のうち、ごく一部なので、全表検索と索引検索では、極端に検索時間が違う。
ということなんだと思います。(推測なので、実は違うかも知れません)

オラクルのマニュアルで、パフォーマンスチューニングガイドというマニュアルがあると思うので、
一読されることをお勧めします。


ちなみに、お書きになったSQLは何か変です。
内部結合のSQLは、bの項目を返さないので、実質的にbに存在するかのチェックを行っているに等しいものです。
書き方や流儀の問題で、内部結合が良いのか、exists条件が良いのか、in条件(メンバーシップ検査)が良いのか
変わってくると思いますが、一応理解できるものです。
しかし、外部結合のSQLについては、bを検索するけど、何もしない??理解に苦しむSQLになっています。
基本的に、内部結合より遅くなる外部結合を、わざわざ書いてみた、というように感じます。
(一般論ですが、同じ書き方をしたら、外部結合は内部結合と同等以下です)

オラクルのオプティマイザは、テーブルの検索の方法を決めますが、外部結合と内部結合で
立案される実行計画に違いがあり、内部結合の方が合理的な検索方法を立案しているので
結果的に、内部結合の方が速いという話なんですが・・

実行計画を見なければ、判りませんが、
・外部結合はbに対して全表検索を選択した。
・内部結合では、bを索引検索を選択した。
・bの検索量が、bの格納件数のうち、ごく一部なので、全表検索と索引検索では、極端に検索時間が違う。
ということなんだと思います。(推...続きを読む

Q引数によってwhere句を切り替える方法

PL/SQLで開発しています。
一つのテーブルで検索条件(引数)によって検索するカラムを切り替えたいのですが、
カーソルをいくつも作らず、
一つのSELECT文で書く方法ってないのでしょうか?
OR条件などを使って考えたのですが、うまくいきません。。

(例)
SELECT *
FROM 売り上げ情報
WHERE (品番 = 引数1 OR 商品名 = 引数2)

引数が無い時は"引数1"・"引数2"がNULLになるので、
カラムがPKなどになっていれば引数が片方しかなくてもうまく動作するのですが、
制限などがかかっていなくNULLで登録されているデータが存在すると余計なデータまでとれてしまいます。
DECODEなどでカラムに存在しない値に変換しようかとも思ったのですが、
なんか不自然な気が@@;
いい方法があれば教えてください。

Aベストアンサー

※ 前の投稿は不完全な状態でした。

「NULLで登録されているデータが存在すると余計なデータまでとれてしまいます」
と言っていますが「引数が無い場合、NULLデータが抽出から漏れる」という事では
ないでしょうか?
where句でNULLデータは「is null」でないと真になりません。

【対応策】
where 引数1 is null
    or (引数1 is not null and 品番 = 引数1)
   or
   引数2 is null
    or (引数2 is not null and 商品名 = 引数2)


人気Q&Aランキング

おすすめ情報