SQLで、２つのテーブルを横に連結し、日付とidの大きい順に抽出したい。

Question

「SQLで、２つのテーブルを横に連結し、日付とidの大きい順に抽出したい。」

MySQLをJAVAで書いています。

下記のように２つの内容が違うが結合キーを持ったテーブルがあります。
「■６．テーブル抽出後のイメージ」のように、抽出するにはどのようなＳＱＬ文を
書けばよいでしょうか？

※前回、自作のＳＱＬ文を書いて、同じような質問をしようとしたのですが、
間違ったＳＱＬ文を提示することで、逆に混乱を回答者様へ与えてしまったので、
今回は、あえて文字で、条件等を書いてみました。

---

■１．２つの構造の違うテーブルがあります。
テーブルＡ
テーブルＢ

■２．共通キーは、２つ。
com_id // 会社ID
cus_id // 顧客ID

■３．最大抽出数は、相互に１００件。

■４．抽出優先度は、各テーブルのdatetimeの大きい順、次に、idの大きい順。

■５．テーブルＡとテーブルＢ共、テーブル内の全項目を抽出します。

■６．テーブル抽出後のイメージ
テーブルＡ テーブルＢ

id com_id cus_id name datetime ... id com_id cus_id address datetime ...
6 1 1 鈴木1 2015-08-01 11:.. 14 1 1 東京1 2015-08-01 11:..
13 2 1 東京2 2015-07-21 22:..
7 1 1 鈴木1 2015-07-15 18:.. 9 1 1 東京1 2015-07-15 18:..
5 5 1 鈴木4 2015-07-12 11:..
4 8 1 鈴木5 2015-07-01 23:.. 10 8 1 東京8 2015-07-01 23:..
3 67 2 鈴木5 2015-06-21 10:.. 11 67 2 東京67 2015-06-21 10:..
12 1 33 東京1 2015-06-18 11:..
2 1 1 鈴木1 2015-06-13 11:.. 8 1 1 東京1 2015-06-13 11:..
1 4 4 鈴木4 2015-06-05 12:..

#sqlとは関係ないですが、
等幅フォントの指定方法がわからなかったので、ブラウザによっては、
表がキレイに揃ってないこがあります。

■７．上記の様に片方にしかレコードがない場合、
0 や null で、レコードが無い方を埋める。

■８．両方に共通であるが、テーブルでの連携と関係ない項目に、
delete_flag = true/false があるので、trueのレコードのみ抽出対象。

以上です。

難しいでしょうか？

Comments

[hirohiro]

idやdatetimeがテーブルAテーブルB両方にあって値が異なる場合、ソートはどちらを優先するのでしょう？

[退会済みユーザー]

テーブルAです。

[退会済みユーザー]

難しいですね。自分で再検討します。

Answer 1

皆さま、ＳＱＬのご提供やご提案ありがとうございました。
私の未熟でエラーがとれなかったり、処理が遅いなど、どれも実現がむずかしいので、
再度、根本から、見直すことにしました。

大変お手数をおかけしたのにとても心苦しく感じております。
この度は、ご回答ありがとうございました。
お手数をおかけして申し訳ございませんでした。

Answer 2

■３．最大抽出数は、相互に１００件。

合計で100件でも、100件ずつでも微妙に「相互に」では無いように思いますが、ここでは前者を採用しています。
つまり以下のコードを実行すると最大100件抽出されます。

delete_flag = true/false があるので、trueのレコードのみ抽出対象。

これって、どちらか片方のdelete_flagがtrueなら採用ですよね？
テーブルAとテーブルBのフラグが共に真でないと駄目な場合は修正が必要です。

datetimeとidは、テーブルAにデータが無い場合のみテーブルBのデータを使ってソートします。
テーブルAにレコードがあってもdatetimeがNULLならテーブルBのdatetimeを採用します。

以上のような条件で良ければ、恐らく次のSQLでいけると思います。

SELECT *
FROM (
  SELECT a.*,b.*
    , COALESCE(a.datetime, b.datetime) AS sort_date
    , COALESCE(a.id, b.id) AS sort_id
  FROM テーブルA a
  LEFT OUTER JOIN テーブルB b ON a.com_id = b.com_id AND a.cus_id = b.cus_id
  WHERE a.delete_flag IS TRUE OR b.delete_flag IS TRUE
  
  UNION
  
  SELECT a.*,b.*
    , COALESCE(a.datetime, b.datetime) AS sort_date
    , COALESCE(a.id, b.id) AS sort_id
  FROM テーブルA a
  RIGHT OUTER JOIN テーブルB b ON a.com_id = b.com_id AND a.cus_id = b.cus_id
  WHERE a.delete_flag IS TRUE OR b.delete_flag IS TRUE
) AS tb
ORDER BY sort_date DESC, sort_id DESC
LIMIT 100

※動作検証してないので、エラーがあるかも知れません。

Comments

[hirohiro]

PostGreSQLなら「 WHERE a.delete_flag IS TRUE OR b.delete_flag IS TRUE」は外に出してもオプティマイザが両方で実行してくれたように思いますが、mySQLはどうなんでしょう？最後に実行するだけで会ったとしても、レコード数が少なくてメモリ使用量を気にしないなら外に出しても中に置いても同じ結果は得られると思います。

[退会済みユーザー]

ＳＱＬありがとうございます。大変助かります。
「どちらか片方のdelete_flagがtrueなら採用」の場合は、tureの方のレコードだけ採用です。
お手数をおかけいたしました。ありがとうございます。

[hirohiro]

>　「どちらか片方のdelete_flagがtrueなら採用」の場合は、tureの方のレコードだけ採用です。
これは微妙に意味が違いまして。
AとBをJOINで繋げると。
tableA.delete_flag = false, tableB.delete_flag = true こういうケースも存在しうると思うのです。
このレコードは抽出するのか捨てるのか？という話でして、普通に考えると抽出するよね。ということで、書いたSQLでは省かないようにしています。

[退会済みユーザー]

delete_flag=trueの方のレコードだけ抽出し、delete_flag=falsの方は、nullや0で埋めて抽出したいです。難しければ、抽出後に、各項目の値を取り出す時に、delete_flagの値でプログラム処理で、nullや0で埋めます。

[退会済みユーザー]

[書いたSQLでは省かないようにしています。 ]、補足のコメント有難うございます。この一言で、抽出した物が正しいかの見方が変わるので、とてもたすかります。
お手数をおかけしました。有難うございます。

Answer 3

MySQLにはFULL OUTER JOINがないので、性能を度外視するのであれば

SELECT 各テーブルのカラム FROM tableA
LEFT OUTER JOIN tableB ON tableA.com_id = tableB.com_id AND tableA.cus_id = tableB.cus_id
WHERE
tableA.delete_flag = 1
AND
tableB.delete_flag = 1
UNION 
SELECT 各テーブルのカラム FROM tableA
RIGHT OUTER JOIN tableB ON tableA.com_id = tableB.com_id AND tableA.cus_id = tableB.cus_id
WHERE
tableA.delete_flag = 1
AND
tableB.delete_flag = 1

のようにUNIONを使うことで対応できます。
ORDER BYはこの段階では使えないので、もう一回SELECTで囲んで
ORDER BYすれば良いと思います。

Comments

[退会済みユーザー]

ご回答ありがとうございます。
select文で、全部を囲むということですか？

[退会済みユーザー]

よくわからないですが、このsqlだと、同じsqlをunionしているように、見えます。

[hirohiro]

横から失礼します
上は「LEFT OUTER JOIN」下は「RIGHT OUTER JOIN」なので同じものをUnionしてるわけじゃないですね

[anonymouskawa]

hirohiroさんの仰る通り、LEFT OUTER JOINとRIGHT OUTER JOINで動作が異なります。
tableAにあって、tableBにない場合でも表示するLEFT OUTER JOINと
tableAに無くても、tableBにある場合に表示するRIGHT OUTER JOINを組み合わせて
tableA、tableBを両方表示するようにしているのです。

前提が100件ということで、これでも良いかなと思いましたが、件数が増えれば性能が低下します。
その場合は、設計自体から見直しが必要になるでしょう。

例に表示したSQLのまま、ORDER BYをつけると、後者のSELECT文にのみORDER BYがかかります。
ですので、一旦select文を外側に書いて、fromに入れてしまえば、ORDER BY句は正常に動作します。
(hirohiroさんの回答がそうなっています)

[退会済みユーザー]

ご説明ありがとうございます。
つくずく、私の知識の無さ、勉強不足（始めたばかりの初心ではありますが、それにしても非常識すぎますね）、を身に染みて思います。
ご丁寧なご説明、大変お手数をお掛けいたしました。
おかげで理解できました。心より、ありがとうございます。