Scenario
The Provost's office needs a single combined contact list for a faculty governance meeting: every tenured faculty member, plus every faculty member currently serving as an academic advisor. Both groups should appear in one result with a role label. Two things to watch. Several people are in both groups, and they should appear once per role, because UNION removes duplicate rows and not duplicate names. And advisor assignments carry history - a student who has been reassigned has a superseded row alongside their current one - so only assignments with saa_is_active = 'Y' count as current. UNION also collapses the many assignment rows a single advisor holds down to one row per advisor.
Expected output
43 rows total - 16 Tenured Faculty rows and 27 Academic Advisor rows. Twelve people hold both roles and so appear twice, once under each role, because UNION removes duplicate rows rather than duplicate names. Sorted by role ascending, then contact_name ascending.