UnionQuery
概要
基本概念
基点テーブルに対する(一つのSQL内での)二つ以上の結果セットを統合します(union句の利用)。
union all も可能です。業務的に重複がないことが確定するのであれば、(SQL内部での)重複チェックが行われない union all の方が(若干)パフォーマンス的に優位です。
パフォーマンス考慮としての union に関するポリシーがあります。
会話上では、ゆにおんくえり もしくは単に ゆにおん と表現します。また、union で統合される中の一つの select 文のことを UnionQuery と表現することもあります。
実装方法
実装の流れ
まずは通常通り、取得対象のテーブルを確定します。基点テーブルだけであれば、単に new するだけ。関連テーブルがある場合は、SetupSelect を指定。その後、union() を呼び出して、UnionQuery のコールバック実装を引数に指定します。
e.g. 会員の名称だけを取得 (Eclipseでコード補完) @Java
MemberCB() cb = new MemberCB();
cb.setupSelect_MemberStatus(); // 取得テーブル(カラム)を確定させて
cb.query().setMemberName_PrefixSearch("S"); // メインの絞り込み条件
cb.u // .u と打って enter (union all なら、.uA + enter)
--
// メソッドが補完されて、引数の "unionQuery" が選択状態に
cb.union(unionQuery)
--
// "new " (new + 空白一つ) と打って ctrl + space そして enter
cb.union(new )
--
// 実装メソッドの空実装が自動生成される (Eclipse-3.5 以上)
cb.union(new UnionQuery<MemberCB>() {
public void query(MemberCB unionCB) {
// TODO Auto-generated method stub
}
})
--
// ctrl (or command) + D で不要な空行やTODOコメントを消して
// UnionQuery での絞り込み条件を指定
cb.union(new UnionQuery<MemberCB>() {
public void query(MemberCB unionCB) {
unionCB.query().setBirthdate_GreaterEqual(toDate("2000/01/01"));
}
});
cb.query().addOrderBy_Birthdate_Asc(); // order-by は union の後に設定
UnionQuery 内で SetupSelect をする必要はありません(してはいけません)。メインの ConditionBean での SetupSelect が UnionQuery に引き継がれます。また、SpecifyColumn も同様です。
また、OrderBy でのカラムへの参照はエリアス名で解決されます。また、OrderBy に関する幾つかの機能制限があります。
e.g. 会員名称が "S" で始まる、もしくは、生年月日が 2000/01/01 以降 の会員 @DisplaySql
select dfloc.MEMBER_ID as c1, dfloc.MEMBER_NAME as c2, ...
, dfrel_0.MEMBER_STATUS_CODE as c14, dfrel_0.MEMBER_STATUS_NAME as c15, ...
from MEMBER dfloc
left outer join MEMBER_STATUS dfrel_0 on dfloc.MEMBER_STATUS_CODE = dfrel_0.MEMBER_STATUS_CODE
where dfloc.MEMBER_NAME like 'S%' escape '|'
union
select dfloc.MEMBER_ID as c1, dfloc.MEMBER_NAME as c2, ...
, dfrel_0.MEMBER_STATUS_CODE as c14, dfrel_0.MEMBER_STATUS_NAME as c15, ...
from MEMBER dfloc
left outer join MEMBER_STATUS dfrel_0 on dfloc.MEMBER_STATUS_CODE = dfrel_0.MEMBER_STATUS_CODE
where dfloc.BIRTHDATE >= '2000-01-01'
order by c6 asc
UnionQuery の連結
UnionQuery を何度も連結させることができます。
e.g. UnionQueryの複数回の連結 @Java
cb.union(new UnionQuery<MemberCB>() {
public void query(MemberCB unionCB) {
cb.query().setMemberStatusCode_Equal_Formalized();
}
});
cb.unionAll(new UnionQuery<MemberCB>() {
public void query(MemberCB unionCB) {
cb.query().setBirthdate_IsNotNull();
}
});
cb.union(new UnionQuery<MemberCB>() {
public void query(MemberCB unionCB) {
cb.query().setFormalizedDatetime_IsNotNull();
}
});
他の機能との組み合わせ
ExistsReferrer や (Specify)DerivedReferrer などの SubQuery 内でも UnionQuery は利用できます。また、そういうときでも、発行されるSQLの整形が乱れることはありません。
e.g. ExistsReferrer の中で UnionQuery @Java
cb.query().existsPurchaseList(new SubQuery<PurchaseCB>() {
public void query(PurchaseCB subCB) {
subCB.query().setPaymentCompleteFlg_Equal_True();
subCB.union(new UnionQuery<PurchaseCB>() {
public void query(PurchaseCB unionCB) {
unionCB.query().setPurchaseCount_GreaterEqual(2);
}
});
}
});
e.g. ExistsReferrer の中で UnionQuery @DisplaySql
where exists (select sub1loc.MEMBER_ID
from PURCHASE sub1loc
where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
and sub1loc.PAYMENT_COMPLETE_FLG = 1
union
select sub1loc.MEMBER_ID
from PURCHASE sub1loc
where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
and sub1loc.PURCHASE_COUNT >= 2
)
メソッド仕様
必ず SetupSelect の後に指定
呼び出しの順番の制約として、UnionQuery は必ず SetupSelect および SpecifyColumn の後に指定します。順番通りでない場合は例外が発生します。
必ず OrderBy の前に指定
呼び出しの順番の制約として、UnionQuery は必ず OrderBy の前に指定します。順番通りでない場合は例外が発生します。
OrderBy で case when 構文がNG
UnionQuery を使った場合、ManualOrder はサポートされません。また、NullsFirst/Last は、case when 構文で実現する DBMS の場合にサポートされません。
UnionQuery の中の UnionQuery
無意味ですが、特に例外にもならず、無視されます。
パフォーマンス考慮での union
union は、or 条件の代替として利用されることがあります。or 条件は、テーブルスキャンが発生してしまう可能性がありますが、union に分解すれば、それぞれの select 文でインデックスが利用され(されるように条件を書く)、パフォーマンス的に安定するためです。
但し、昨今のDBMSでは(2010/11/05時点)、or 条件でもインデックスを利用し、極端なパフォーマンス劣化を防ごうという動きもあります。 例えば、MySQL-5.1のインデックス結合最適化(index_merge)など。そういう意味では、あまり union にこだわる必要はないかもしれませんが、それに任せてしまって良いかどうかは結局アプリのパフォーマンス要件の厳しさや状況次第になるかと思います。
union でないと実現できない、or 条件でないと実現できない、という明確な要件がある場合を除くとして、結論としては、 両方試して実行計画や実行時間を吟味するのが一番というところです。 少なくともDBFluteでは、仮に union の方がパフォーマンスが良いという状況の場合に、別の余計な要素で union の利用を止めてしまうようなことが無いように努めています。union と聞くと "SQL書くのが面倒そう" と思われる方が多いと思われますが、確かに外だしSQLで書けば入力文字は圧倒的に増えますが、ConditionBean であれば、コールバックで絞り込み条件を指定するだけとなります(select 句や from, join 句は自動解決)。 また、"SQLが見づらくなりそう" という問題も、union を使ってもしっかり整形されたSQLとなります。
e.g. 会員名称が "S" で始まる、もしくは、生年月日が 2000/01/01 以降 の会員 @Java
MemberCB cb = new MemberCB();
cb.setupSelect_MemberStatus();
cb.query().setMemberName_PrefixSearch("S");
cb.union(new UnionQuery<MemberCB>() {
public void query(MemberCB unionCB) {
unionCB.query().setBirthdate_GreaterEqual(toDate("2000/01/01"));
}
});
e.g. 会員名称が "S" で始まる、もしくは、生年月日が 2000/01/01 以降 の会員 @DisplaySql
select dfloc.MEMBER_ID as c1, dfloc.MEMBER_NAME as c2, ...
, dfrel_0.MEMBER_STATUS_CODE as c14, dfrel_0.MEMBER_STATUS_NAME as c15, ...
from MEMBER dfloc
left outer join MEMBER_STATUS dfrel_0 on dfloc.MEMBER_STATUS_CODE = dfrel_0.MEMBER_STATUS_CODE
where dfloc.MEMBER_NAME like 'S%' escape '|'
union
select dfloc.MEMBER_ID as c1, dfloc.MEMBER_NAME as c2, ...
, dfrel_0.MEMBER_STATUS_CODE as c14, dfrel_0.MEMBER_STATUS_NAME as c15, ...
from MEMBER dfloc
left outer join MEMBER_STATUS dfrel_0 on dfloc.MEMBER_STATUS_CODE = dfrel_0.MEMBER_STATUS_CODE
where dfloc.BIRTHDATE >= '2000-01-01'
