特定のテーブルにある複数条件のデータと一致するもののみを、別のテーブルから抜き出したい。

Question

前提・実現したいこと

以下の図のように、二つのテーブル(table_joinとlink_table)から、link_tableにある
複数カラムを使って複数の条件（たとえばfile_name, sheet_nameがそれぞれ特定の値）を満たす
物のみをtable_joinから取り出したいです。

追記：Unique、Primaryは、idにしか設定していません。

お聞きしたいことは下記2点です。
①「該当のソースコード」に記載の内容で書いたところ、エラーは出なかったのですが、
クエリとして上記のユースケースとして問題はないでしょうか。
（上の図ではすごく少ないデータのようなので問題なさそうに見えますが、実データは数十万件の
データであり、想定通りのクエリになっているかの確認が難しいです。）
②①では、table_joinを検索するキーを「id」で行っているのですが、「year」「tag」で行った場合（a.id, c.idを、(a.year and a.tag), (c.year and c.tag)に書き換えた）、検索される件数が変わってしまいます。紐づけ対象として行っているだけなので、結果は等価と思ったのですが、等価にならない理由として想定されるものはどういうことになりますでしょうか。

該当のソースコード

select a.id, a.a_data1, a.b_data1 from table_join a 
where 
 a.id 
in 
 (select c.id from link_table c where c.file_name = 'ほげほげ.xlsx' 
and
 c.sheet_name = 'a')
UNION
 select a.id, a.a_data1, a.b_data1
from 
 table_join a
where
 a.year >= 2015

試したこと

sqlに関して、やりたいことを検索し、MYSQL_workbenchなどでデバッグしてみた。

環境

MySQL workbench 8.0.19

Answer 1

①に対して、集計するというのがクエリーでは実現されていません。
union はマージする機能で集計ではありません。

件数については、クエリーの性能を気にされていると思いますが、それは正しいクエリーであると同時に、インデックスも適切でなければなりません。

②について、table_joinとlink_tableの関係はidで1:n（※）なのでしょう。
これに対し、yearとtagではn:nになるので、件数が変わるのは当然です。
※テーブルが分かれているのが正しいなら1:nですが、1:1なら正規化されている状態ではありません。

やりたい事は概ね以下の様なSQLだと思います。

select a.year, a.tag, sum(a_data1), sum(b_data2), sum(b_data1), sum(b_data2)
from  table_join a inner join link_table c
      on a.id=c.id
where c.file_name = 'ほげほげ.xlsx' 
  and c.sheet_name = 'a'
  and c.year >= 2015
group by a.year, a.tag

Comments

[H.K2]

ご回答ありがとうございます!帰ったら確認してみます!

[H.K2]

自分なりに調べてみました。Unionというのは、２クエリの条件を重複除いてANDするということなので、自分が書いたクエリだと、「ファイル名がほげほげであることと、シート名がAであること、あと年度が２０１５以上」の３条件それぞれをANDして、重複列を削除した、という認識でよいでしょうか。
なので、groupbyがないから「集計はされない」という理解でよいでしょうか。
（集計はされないけど、式としてはANDになっている？）
また、groupby でsumを除いた場合は、合計値ではなく、最初の値になっているのでしょうか。
https://gotto50105010.hatenablog.com/entry/2018/12/06/223615

[H.K2]

後すみません。もう一点だけ。とある事情でもし、２テーブル間のseqのみがずれてしまうリスクがある場合(year,tagはずれない、かつ、yearとtagの組み合わせ自体は一意という前提）、onにseqの代わりにyear, tagを指定してもよいでしょうか。
on a.id=c.idの代わりに、on (a.year=c.year and a.tag=c.tag)にかえて、サンプルでやってみたところでは抽出件数が変わらなかったので、問題ないのかとは思いますが、念のためご教示いただけましたら幸甚です。

[H.K2]

あ、なんどもすみません。。。ベン図みたら、ANDではないですね…。「ファイル名がほげほげかつシート名がA」であること　または　「yearが2015以上」で、重複除去だから、ご提示いただいたクエリより多くなる、ということなのかな…。

[sazi]

テーブルの定義を質問に追加して下さい。（プライマリーおよびユニーク制約など）
結合して良いかとか集計して良いかなどは、そういった情報が無ければ答えられません。

[sazi]

union の理解はまだできていないようですね。
unionするだけで条件が別の集合に付加されることはありません。

[H.K2]

すみません。追記しました。primaryとuniqueはidにしかありません。（mysqlが自動で出力する）
ただ、運用の問題だと思うのですが、2テーブルのidがずれてしまうときがあり、同じIDでも別のものをさしてしまうことがある(片方のテーブルのIDの更新が追従しないときがある)ので、仕組み上一意になる、「yearとtagの組み合わせ」でonできないのかなと思った次第です。（year単独では一意にはならないですが、その場合、andを付けたとしても、onで結合したらまずいのでしょうか。）

[sazi]

「yearとtagの組み合わせ」が一意なら、idの代わりの結合条件にしても大丈夫ですけど、それなら何故テーブルを分けているんでしょうね。

[H.K2]

２つのテーブルを別々のツールで変更しているからだと思います。
（仕組み上はまずいとは思うのですが、いろいろとあってかえられない様なので…。
ともあれ、ありがとうございます。UNIONは、pythonのpandasのところで図解があったので、なんとなくイメージがつかめました。確かに自分が書いたクエリではだめそうですね。ありがとうございます！）
https://ai-inter1.com/pandas-dataframe_union/