複数テーブルの結合について

Question

表記の件につきまして質問します。

テーブル１（買い物かご）

id	item_code	name
1	a123	りんご
2	b123	白菜
3	c123	みかん
4	d123	青葉
5	e123	メロン

上記、買い物かごテーブルを以下のマスタテーブルに結合してmakercodeを取得する事を目的としています

マスターテーブルは２つあり、マスタ１に無いデータは、マスタ２にありますが、マスタ２にあるデータはマスタ１にある場合があります。

テーブル２（マスタ1）
|id|item_code|name|makercode|subcode|
|:--|:--:|--:|--:|
|1|a123|りんご|aaa|aa1|
|2|b123|白菜|aaa|ds2|
|3|c123|みかん|bbb|ed3|
|4|d123|青葉|ccc|vf4|

テーブル２（マスタ2）
|id|item_code|name|makercode|label|
|:--|:--:|--:|--:|
|1|d123|青葉|ccc|wnsd|
|2|e123|メロン|ccc|asde|

この為、結合する際、買い物かごとマスタ１を結合させる際、マスタ１に結合できるデータが無い場合のみマスタ２のデータを結合するように考えております。

尚、マスタ１とマスタ２の合計データ数は10万件ほどになります。予めマスタ1とマスタ２のデータを結合する方法もございますが、ユニークなデータでなくなる為、マスタ同士を結合する場合はユニークにする点も考慮頂ければと思います。

この対応方法についてSQLクエリにてご教授頂けますよう質問いたします。

Answer 1

unionでマスタ1とマスタ2の重複を排除してます。

drop table if exists basket;
drop table if exists master1;
drop table if exists master2;

create table basket(item_code varchar(20),name varchar(20));
insert into basket values
('a123','りんご'),
('b123','白菜'),
('c123','みかん'),
('d123','青葉'),
('e123','メロン');

create table master1(item_code varchar(20),name varchar(20),makercode varchar(20),subcode varchar(20));
insert into master1 values
('a123','りんご','aaa','aa1'),
('b123','白菜','aaa','ds2'),
('c123','みかん','bbb','ed3'),
('d123','青葉','ccc','vf4');

create table master2(item_code varchar(20),name varchar(20),makercode varchar(20),lavel varchar(20));
insert into master2 values
('d123','青葉','ccc','wnsd'),
('e123','メロン','ccc','asde');

with master as (
select
  item_code
  ,name
  ,makercode
from
  master1
union
select
  item_code
  ,name
  ,makercode
from
  master2
)
select
  b.*
  ,m.makercode
from
  basket as b
left join
  master as m
on
  b.item_code = m.item_code;

Answer 2

UNION ALLでFULL JOINのまねをしてみました。

SELECT * FROM `買い物かご` t1
JOIN (
    SELECT coalesce(m1.id,m2.id) id, coalesce(m1.item_code,m2.item_code) item_code, coalesce(m1.name,m2.name) name,
    coalesce(m1.makercode,m2.makercode) makercode, coalesce(subcode,label) subcode
    FROM
    `マスタ１` AS m1
        LEFT OUTER JOIN
    `マスタ２` AS m2 ON m1.item_code = m2.item_code
    UNION ALL
    SELECT coalesce(m1.id,m2.id), coalesce(m1.item_code,m2.item_code), coalesce(m1.name,m2.name),
    coalesce(m1.makercode,m2.makercode), coalesce(subcode,label)
    FROM
    `マスタ１` AS m1
        RIGHT OUTER JOIN
    `マスタ２` AS m2 ON m1.item_code = m2.item_code
    WHERE
    m1.item_code IS NULL
) m3
ON t1.item_code=m3.item_code
;


id item_code name    id      item_code name makercode subcode
4       d123    青葉    4       d123    青葉    ccc     vf4
1       a123    りんご  1       a123    りんご  aaa     aa1
2       b123    白菜    2       b123    白菜    aaa     ds2
3       c123    みかん  3       c123    みかん  bbb     ed3
5       e123    メロン  2       e123    メロン  ccci    asde

Answer 3

こんな感じでしょうか？

create table tbl1(id int,item_code varchar(20),name varchar(20));
insert into tbl1 values
('1','a123','りんご'),
(2,'b123','白菜'),
(3,'c123','みかん'),
(4,'d123','青葉'),
(5,'e123','メロン');

create table tbl2(id int,item_code varchar(20),name varchar(20),makercode varchar(20),subcode varchar(20));
insert into tbl2 values
(1,'a123','りんご','aaa','aa1'),
(2,'b123','白菜','aaa','ds2'),
(3,'c123','みかん','bbb','ed3'),
(4,'d123','青葉','ccc','vf4');

create table tbl3(id int,item_code varchar(20),name varchar(20),makercode varchar(20),lavel varchar(20));
insert into tbl3 values
(1,'d123','青葉','ccc','wnsd'),
(2,'e123','メロン','ccc','asde');

select t1.id,t1.item_code,t1.name
,coalesce(t2.makercode,t3.makercode) as makercode
from tbl1 as t1
left join tbl2 as t2 on t1.item_code=t2.item_code
left join tbl3 as t3 on t1.item_code=t3.item_code

Answer 4

マスタ1とマスタ2をLEFT JOINしてNULLにならないフィールドの方をIFで使う、っていうのでどうですか？