Q&A
id が同じものを指すユニークなキーだとして join ではなく union する理由は何ですか?
###実現したいこと
カラムが異なるテーブルを縦にUNIONするとき、一方にないカラムに自動でnullを入れる方法はございませんか？
###該当のソースコード
以下のように片方にはnameがあり、もう片方にはaddressがあるようなケースです。
SQL
1CREATE TABLE table_one 2 (`id` int, `name` text, `notes` text) 3; 4INSERT INTO table_one 5 (`id`, `name`, `notes`) 6VALUES 7 (1, 'mina', 'val_1'), 8 (2, 'mari', 'val_2'), 9 (3, 'mako', 'val_3') 10; 11 12CREATE TABLE table_two 13 (`id` int, `address` text, `notes` text) 14; 15INSERT INTO table_two 16 (`id`, `address`, `notes`) 17VALUES 18 (1, '東京', 'val_3'), 19 (2, '神奈川', 'val_2'), 20 (3, '千葉', 'val_1') 21;
はじめてUNIONを使うのでよくわかっていないかもしれませんが、自分でつなげると次のように１つずつnullを指定する方法までしかたどり着けませんでした。
SQL
1SELECT id 2 ,name 3 ,'null' as address 4 ,notes 5FROM table_one 6UNION 7SELECT id 8 ,'null' as name 9 ,address 10 ,notes 11FROM table_two 12ORDER BY notes; 13
しかし実際には多くのカラムが異なるテーブル同士のUNIONを想定しています。
上記ではSELECTは４個ですが実際には２０個くらいになりそうなので、このnullを自動で入れる方法があればと思い質問させて頂きました。
良い方法がございましたら宜しくお願い致します。
###補足情報（FW/ツールのバージョンなど）
phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。
失礼いたしました。「id が同じものを指すユニークなキー」ではなく、別のものを指します。下記のcommentとthreadが（voice_idとthread_idが）それです。
実際には下記のように「# user_id=1さんのタイムラインに流したいcommentとthreadを取得」としてます。
http://sqlfiddle.com/#!9/de2499/11
このときthreadとcommentは別々に取得されますので、それらを縦に連結するためにunionを使おうと思いました。
そしてご覧のように「null as xxx」という指定が大量にあるため、この指定がなくてもnullを入れる方法があれば、というのが質問の経緯です。
回答5件
0
そもそものテーブル設計を見直した方が良いのでは？
要件不明瞭な部分があるので一概に言えませんが・・・。
union自体がそもそもカラム数・順番・型が一致していることを前提として
結果セットを結合するものなので、「カラムが一致しない部分をNULLで」となると
設計自体を見直す必要があるのではと思います。
無理やりnullを入れて結合しなければならない設計はよろしくないと思います。
投稿2020/02/11 22:19
総合スコア80875
アドバイスありがとうございます。
早速ですが「お気に入りリストの取得」という要件として知りたいです。
Aさんが「東京」と「ｍina」を「お気に入り」しているとき、Aさんの「お気に入りリスト」を表示すると、次のようにnullとせざるをえないのでは、と考えています。
id | name | address | notes
1 | mina | (null) | val_1
1 | (null) | 東京 | val_3
このような要件ですが、null以外に良い解決策があればぜひ知りたいです。
宜しくお願い致します。
同じIDで1対1なら別テーブルにするメリットはありません。
質問に「２０個くらいカラムが違う」とあるように、もし別テーブルにしないとnullのカラムが４０個くらいになってしまいますので、ここは別にしたいのですが、そうなるといかがでしょうか。
「くらい」だけの情報で応えるのは不可能です。
機能要件、非機能要件含めて総合的に考える必要があるからです。
「どうやるか(How)」にばかりとらわれてるようなので、話が進んでません。
現在の作りのままいこうとするとnullって固定で入れるしかありません。
設計がまずいからそうするしかないわけですよね。
それなら総合的な観点で全て見直して「情報が無理なく取得できるように」構造から見直すしかありません。
例え自動でnullと入れられたとして、それってアプリケーションから使いやすい形になってますか？
考えてみてください。
何を作るのか、なぜ作るのか。
ありがとうございました。よく考えてみます。それでは失礼致します。
はい。既に書いたように、現状の質問内容では他人にはアドバイス不可能です。
他の回答も「設計を見直すべき」にとどまっています。
0
カラムが異なるテーブルを縦にUNIONするとき
そもそも構造が違うテーブルをUNIONすること自体、適切かどうか考え直したほうがいいでしょう。
別々に取ってきてアプリケーションレベルで統合、というほうがいいのかもしれません。
投稿2020/02/12 00:22
総合スコア146018
ありがとうございます。アプリケーションレベルといいますと、別々にSQLでとってきたものをPHPで配列にし、それを統合してソートする、ということでしょうか？SQLよりも早いなど、何か効果があるのでしょうか？
> SQLよりも早いなど、何か効果があるのでしょうか？
バラバラなデータ構造でも気にせず1本の配列に入れられます。
SQLの結果が非統一なことと、PHPの配列が非統一であることについて、わかりやすさの違いがいまいち、初学者の私には恐れ入りますが腑に落ちない印象を受けます。同じ非統一なら、別々に２回のSQLを流しさらにPHPでソートするのは遅いばかり、ということはないのでしょうか？
> SQLの結果が非統一なことと、PHPの配列が非統一であることについて、わかりやすさの違いがいまいち
「まとめて取得するSQLが作りにくい」こと自体を問題としていたのではないのですか？バラバラに取得すれば、双方のSQLは簡潔に書けます。
おしゃる通り問題はそこです。ただその問題を解決することと引き換えに速度という別の問題が生じてしまうのであれば、悩ましいです。
もちろんそもそもが仰る構造の問題ではあるのですが、初学者の私に完全な正規化は困難なので、一部の方に有難いお叱りを頂きつつも現時点での最善をなんとか模索しているところでございます。
UNIONの結果全体にソートをかけるのであれば、インデックスがかかる余地もありませんので、PHPでやったから致命的に遅くなることもないかと思います（もとからそんなに速くない、ということです）。
なるほど、たしかにそうかもしれません。根気よくお付き合い頂きまして誠にありがとうございます。
0
しかし実際には多くのカラムが異なるテーブル同士のUNIONを想定しています。
上記ではSELECTは４個ですが実際には２０個くらいになりそうなので、このnullを自動で入れる方法があればと思い質問させて頂きました。
union しなくてはならない場合というのは、正規化がされていない場合です。
一番良い方法は正規化してunionを不要とする事。
union するのは正規化の為の指示みたいなものですから、自動でNullを入れる方法はありません。
正規化をするツールなどはあるようですから、そういったアルゴリズムを利用してSQLを動的に生成するような仕組みを作るならある程度は可能かもしれません。
投稿2020/02/12 00:17
総合スコア25327
昔、某DBMSでorが遅くてunionで対処するというようなテクニック的なものはあったようですけどね。
UNIONは正規化ができていない証左とは、さすがのご慧眼ですね。いつもありがとうございます。はじめての設計なので苦労していますが、また根気よく見直してみたいと思います。今後とも宜しくお願い致します。
アンチパターンですけど、EAVにすれば(項目名は揃えて、区分などで内容を表す）取りあえずUinonは不要になります。
ですが、selectでcaseが必要になるので手間は一緒ですけどね。
なるほど、まさかこういうケースで必要になってくるのがEVAだったとは。ご存じかもしれませんが以前はEVAだった設計をやめて今回躓いてしまったので、またEVAにするのも悔しいですね。とりあえずEVAを使えるケースとして覚えておきます。いつも良い指針をどうもありがとうございます。
0
ベストアンサー
procedureでそういうふうに作ればいけるでしょう
いわゆるSQL文にはそういう書式は用意されていないと思います
※アップロード制限のため長文回答ができません
コメント欄を参照ください
投稿2020/02/12 00:15
編集2020/02/12 03:13
総合スコア116724
drop procedure if exists dynamic_union;
delimiter //
create procedure dynamic_union(in t1 varchar(100),in t2 varchar(100))
begin
create temporary table tmp0(sort int primary key auto_increment,c0 varchar(50));
set @sql:=concat(
"insert into tmp0(c0)
select distinct column_name
from
information_schema.columns
where
table_schema = '",database(),"' and
table_name in('",t1,"','",t2,"')");
PREPARE stmt from @sql;
EXECUTE stmt;
set @sql:=concat(
"create temporary table tmp1 as
select column_name as c1
from
information_schema.columns
where
table_schema = '",database(),"' and
table_name ='",t1,"'");
PREPARE stmt from @sql;
EXECUTE stmt;
set @sql:=concat(
"create temporary table tmp2 as
select column_name as c2
from
information_schema.columns
where
table_schema = '",database(),"' and
table_name ='",t2,"'");
PREPARE stmt from @sql;
EXECUTE stmt;
set @sql:=concat(
"select concat('select ',c,' from ",t1,"') into @sql1 from (
select group_concat(concat(coalesce(c1,'null'),' as ',c0) order by sort asc) as c
from tmp0 left join tmp1 on c0=c1
) as sub1 ");
PREPARE stmt from @sql;
EXECUTE stmt;
set @sql:=concat(
"select concat('select ',c,' from ",t2,"') into @sql2 from (
select group_concat(concat(coalesce(c2,'null'),' as ',c0) order by sort asc) as c
from tmp0 left join tmp2 on c0=c2
) as sub2 ");
PREPARE stmt from @sql;
EXECUTE stmt;
set @sql:=concat(@sql1," union all ",@sql2);
PREPARE stmt from @sql;
EXECUTE stmt;
end
//
delimiter ;
procedure実行
call dynamic_union('table_one','table_two');
procedureというのはMySQLで「すべての処理に対して追加する処理」のようなものでしょうか。今回であれば「UNIONでつなげるとき、自動でnullを入れる」という？期待してしまいますね。
検索したのですがそちらを書く場所がわかりませんでした。もしよろしければ初歩的なことかと思いますがご教授いただけましたら幸いです。
phpMyAdminを利用しているならdbに入った状態で
sql実行欄に「drop procedure」から最後の「delimiter ; 」
までをコピペして実行してください
callの方はその後どのタイミングでも実行できます
procedure便利すぎます。ここまで使いこなせれば業務もさぞ捗っておられることでしょうね。恐れ入りました。質問の要求に的確にお答え頂きまして誠にありがとうございます。
0
この人は間違いを指摘しても直す気がないのが困りものです。
前にも書いているけど無視されていますが、テーブル設計がかなりまずいです。
SQLで null って書くとヌルですが、'null' は文字列です。
UNION とUNION ALLの違い は痛い思いをしないと身につかない人もいます。
投稿2020/02/11 22:43
総合スコア16417
あなたの回答
tips
プレビュー
