with
man_total as (select * from DRINK_HABITS where CATEGORY_CODE = 110 and GENDER_CODE = 2),
woman_total as (select * from DRINK_HABITS where CATEGORY_CODE = 110 and GENDER_CODE = 3),
man_drink as (select * from DRINK_HABITS where CATEGORY_CODE = 120 and GENDER_CODE = 2),
woman_drink as (select * from DRINK_HABITS where CATEGORY_CODE = 120 and GENDER_CODE = 3)

select
PREFECTURE.PF_CODE as CODE,
PREFECTURE.PF_NAME as NAME
from
PREFECTURE
join DRINK_HABITS
on PREFECTURE.PF_CODE = DRINK_HABITS.PF_CODE
where GENDER_CODE in (2,3)
and CATEGORY_CODE = 120
group by PREFECTURE.PF_CODE