ことはじめ

チーム開発演習で、SpringBootのMapperファイルを書いていたんですよね。Mapper.xmlってやつ。 んで、ここがおかしくてバグが生じていたんだけど、JOINについてちゃんと理解していなかったからだった。 JOINには色々あってさ、inner joinとかouter joinとか。 たくさんのテーブルを繋げまっくる時って、どのJOINにするかが大事って話だ。

まずはすごくわかりやすい図があるのでそれで学んでくれ

Left JoinとInner Joinをミスっていた。

  SELECT
  I.ITEM_ID,
  I.ITEM_NAME,
  I.DESCRIPTION,
  I.PRICE,
  I.IMAGE AS ITEM_IMAGE,
  I.UPDATED_AT AS ITEM_UPDATE_AT,
  I.CREATED_AT AS ITEM_CREATED_AT,
  I.DELETE_FLAG AS ITEM_DELETE_FLAG,
  COUNT(I.ITEM_ID = L.ITEM_ID) AS LIKE_COUNT,
  U.USER_ID,
  U.EMAIL,
  U.PASSWORD,
  U.PROFILE,
  U.IMAGE AS USER_IMAGE,
  U.DELETE_FLAG AS USER_DELETE_FLAG,
  U.UPDATED_AT AS USER_UPDATED_AT,
  U.CREATED_AT AS USER_CREATED_AT,
  U.USER_NAME,
  C.CATEGORY_ID,
  C.CATEGORY_NAME,
  C.UPDATED_AT AS CATEGORY_UPDATED_AT,
  C.CREATED_AT AS CATEGORY_CREATED_AT
  FROM
  ITEMS I
  LEFT OUTER JOIN USERS U
  ON I.USER_ID = U.USER_ID
  LEFT OUTER JOIN CATEGORIES C
  ON I.CATEGORY_ID = C.CATEGORY_ID
  LEFT OUTER JOIN LIKES L
  ON I.ITEM_ID = L.ITEM_ID
  WHERE I.ITEM_ID = #{itemId}
  GROUP BY I.ITEM_ID

これ長いけど非常にいいSQLだよね。学ぶことがいっぱいある。それは置いておいて、ここのLEFT OUTER JOINのところ、普通のJOINにすると共通部分しか出て来なくなってしまうんだよね。 で、どこに問題が潜んでいるかというと、

ON I.ITEM_ID = L.ITEM_ID

ここ。Lがどんな構造かというと、

create table if not exists LIKES
(
    LIKE_ID            bigint(20)     auto_increment        not null  primary key,
    ITEM_ID            bigint(20)     not null,
    USER_ID            bigint(20)     not null,
    UPDATED_AT         timestamp(3)   default current_timestamp(3),
    CREATED_AT         timestamp(3)   default current_timestamp(3),
    foreign key (USER_ID) references USERS (USER_ID),
    foreign key (ITEM_ID) references ITEMS (ITEM_ID),
    unique (ITEM_ID,USER_ID)
);

こんな感じで、どのユーザがどのアイテムをお気に入りしたかがわかる中間テーブル的な感じなんだよね。んで、ユーザがお気に入りしていない商品もあるわけで、そういう商品はもちろんLIKES テーブルには記録されない。にもかかわらず、INNER JOINで

ON I.ITEM_ID = L.ITEM_ID

これをしてしまうと、LIKESテーブルにないアイテムは消えてしまうことになる。ってことだ。 だから、LEFT OUTER JOINにしないといけない。で、他のところも全部LEFT OUTER JOINになっているけど、そこだけでよかったって話ですね。お疲れ様です。