Role Comparison between two users using SQL

SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY 
            CASE WHEN r1.ROLE_CODE IS NOT NULL OR r2.ROLE_CODE IS NOT NULL THEN 1 ELSE 2 END,
            CASE 
                WHEN r1.ROLE_CODE IS NOT NULL AND r2.ROLE_CODE IS NOT NULL THEN 1
                WHEN r1.ROLE_CODE IS NOT NULL THEN 2
                WHEN r2.ROLE_CODE IS NOT NULL THEN 3
                ELSE 4
            END,
            COALESCE(r1.ROLE_CODE, r2.ROLE_CODE)
        ) AS ROLE_SEQUENCE,
        COALESCE(r1.ROLE_CODE, r2.ROLE_CODE) AS ROLE_CODE,
        CASE WHEN r1.ROLE_CODE IS NOT NULL THEN 'Yes' ELSE 'No' END AS HAS_USER1,
        CASE WHEN r2.ROLE_CODE IS NOT NULL THEN 'Yes' ELSE 'No' END AS HAS_USER2
    FROM
        (
            SELECT DISTINCT prd.ROLE_COMMON_NAME AS ROLE_CODE
            FROM PER_ALL_PEOPLE_F ppf
            JOIN PER_USERS pu ON ppf.PERSON_ID = pu.PERSON_ID
            JOIN PER_USER_ROLES pur ON pu.USER_ID = pur.USER_ID
            JOIN PER_ROLES_DN prd ON pur.ROLE_ID = prd.ROLE_ID
            WHERE
                TRUNC(SYSDATE) BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE
                AND (
                    (:person_number1 IS NOT NULL AND ppf.PERSON_NUMBER = :person_number1)
                    OR (:username1 IS NOT NULL AND UPPER(pu.USERNAME) = UPPER(:username1))
                )
        ) r1
    FULL OUTER JOIN
        (
            SELECT DISTINCT prd.ROLE_COMMON_NAME AS ROLE_CODE
            FROM PER_ALL_PEOPLE_F ppf
            JOIN PER_USERS pu ON ppf.PERSON_ID = pu.PERSON_ID
            JOIN PER_USER_ROLES pur ON pu.USER_ID = pur.USER_ID
            JOIN PER_ROLES_DN prd ON pur.ROLE_ID = prd.ROLE_ID
            WHERE
                TRUNC(SYSDATE) BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE
                AND (
                    (:person_number2 IS NOT NULL AND ppf.PERSON_NUMBER = :person_number2)
                    OR (:username2 IS NOT NULL AND UPPER(pu.USERNAME) = UPPER(:username2))
                )
        ) r2
    ON r1.ROLE_CODE = r2.ROLE_CODE

    WHERE (:person_number1 IS NOT NULL OR :username1 IS NOT NULL
        OR :person_number2 IS NOT NULL OR :username2 IS NOT NULL)

) 
UNION ALL
SELECT 1 AS ROLE_SEQUENCE, 'No filter selected', NULL, NULL
FROM DUAL
WHERE (:person_number1 IS NULL AND :username1 IS NULL AND :person_number2 IS NULL AND :username2 IS NULL)

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.