with ind as (
    select
        EMAIL
        , instr(EMAIL, '@') as f
        , instr(EMAIL, '.co.jp') as t
    from
        MEMBER_MST
)

select
    mm.MEMBER_CODE as MEMBER
    , mm.EMAIL as EMAIL
    , substr(mm.EMAIL, ind.f+1, ind.t-ind.f-1) as DOMAIN
from
    MEMBER_MST as mm
    left outer join
        ind on
            ind.EMAIL = mm.EMAIL
order by
    DOMAIN desc
    , MEMBER desc
;