WITH /*+ materialize */ t(id1, id2) AS (SELECT 1, 2 UNION ALL SELECT 2, 3 UNION ALL SELECT 3, 4)
SELECT *
  FROM users u
  JOIN t t1 ON u.id = t1.id1
  JOIN t t2 ON u.ROLE = t2.id2