-- MariaDB 10.4 에서 테스트 했습니다. -- WITH t AS ( SELECT 1170 u, 'test3333' room_id, null parent_u, 'aa' custom_u UNION ALL SELECT 1171, 'test3333', null, 'bb' UNION ALL SELECT 1173, 'test3333', null, 'cc' UNION ALL SELECT 1174, 'test3333', null, 'dd' UNION ALL SELECT 1175, 'test3333', null, 'ee' UNION ALL SELECT 1176, 'test3333', 'bb', null ) SELECT a.u , a.room_id , a.parent_u , a.custom_u , b.u FROM t a LEFT OUTER JOIN t b ON a.parent_u = b.custom_u AND a.room_id = b.room_id -- 조인조건 WHERE a.room_id = 'test3333' -- 검색조건 ORDER BY IFNULL(b.u, a.u), a.parent_u ;