WITH t AS ( SELECT 'A회사' cd, 'ab....d' id FROM dual UNION ALL SELECT 'A회사', 'a....cd' FROM dual UNION ALL SELECT 'B회사', 'a1234cd' FROM dual ) SELECT * FROM t b LEFT OUTER JOIN t a -- ON REGEXP_LIKE(b.id, a.id) ON b.id LIKE REPLACE(a.id, '.', '_') AND a.cd = 'A회사' WHERE b.cd = 'B회사' -- AND a.cd IS NULL ;