WITH HANKAKUUME AS (
  SELECT
    EMP_CODE,
    replace(EMP_LAST_NAME, " ", "") AS `EMP_LAST_NAME`,
    replace(EMP_FIRST_NAME, " ", "") AS `EMP_FIRST_NAME`
  FROM
    EMP
),
KABURI AS (
  SELECT
    EMP_LAST_NAME,
    EMP_FIRST_NAME,
    COUNT(1) AS `CNT`
  FROM
    HANKAKUUME
  GROUP BY
    EMP_LAST_NAME,
    EMP_FIRST_NAME
  HAVING
    CNT > 1
)

SELECT
  EMP_CODE AS `CODE`,
  EMP_LAST_NAME AS `SURNAME`,
  EMP_FIRST_NAME AS `NAME`
FROM
  EMP
WHERE
  replace(EMP_LAST_NAME, " ", "") IN (
    SELECT
      EMP_LAST_NAME
    FROM
      KABURI
  )
  AND replace(EMP_FIRST_NAME, " ", "") IN (
    SELECT
      EMP_FIRST_NAME
    FROM
      KABURI
  )
  AND VALID_FLG = '1'
ORDER BY
  replace(EMP_LAST_NAME, " ", ""),
  replace(EMP_FIRST_NAME, " ", ""),
  EMP_CODE