Why is FULL JOIN acting like LEFT JOIN in SQL

Question

My issue is with trying to join tables that don't have primary keys with a full join. I need to generate a table that is joined on COMPANY and Program Year, but the participant doesn't need to participate in both events, just one of the two. The PHA and BIO tables have multiple entries for each user as they participate in multiple years. When I join one of the PHA or BIO tables to the registration table (RL) then try a full join with the remaining table, it essentially acts like a left join.

What I am looking for is outlined in the simple excel example attached. For each company the participation in either of the events could vary (as in BIO could have higher participation one year, but PHA could the next).

Below is some of what I have been experimenting with:

SELECT
    RL.UserName
    ,RL.Company
    ,BIO.ScreeningDate
    ,BIO.ProgramYear
    ,PHA.PHADate
    ,PHA.ProgramYear            

FROM    Registration RL 
        LEFT OUTER JOIN PHADetails PHA
            on RL.UserName = PHA.Username
        FULL OUTER JOIN Biometrics BIO
            on RL.UserName = BIO.Username 
                AND BIO.ProgramYear = PHA.ProgramYear

--FROM  Registration RL 
--      LEFT OUTER JOIN Biometrics BIO
--          on RL.UserName = BIO.Username
--      FULL OUTER JOIN PHADetails PHA
--          on RL.UserName = PHA.Username 
--              AND BIO.ProgramYear = PHA.ProgramYear

WHERE RL.Company = 'City' 
        and CASE WHEN BIO.ProgramYear = 2019 and PHA.ProgramYear = 2019 THEN 1
            WHEN BIO.ProgramYear = 2019 AND PHA.ProgramYear IS NULL THEN 1
            WHEN BIO.ProgramYear IS NULL AND PHA.ProgramYear = 2019 THEN 1 
            ELSE 0 END > 0

-- have 27 participants in biometrics program
-- have 37 particiapnts in PHA program
-- want to always include participants who did both or either one, but
--   the participation for either one will vary
-- need to combined the two left joins shown above into the same full join

Answer 1

Notwithstanding the WHERE clause situation, your current query can also produce undesired results if records that appear in one table does not match by year of record in other table. Consequently, rows with NULL can be returned when there supposedly should be values. For this reason, consider only joining on UserName then running DISTINCT and COALESCE to de-dupe and combine years.

SELECT DISTINCT
    RL.UserName
    ,RL.Company
    ,COALESCE(BIO.ProgramYear, PHA.ProgramYear) AS ProgramYear
    ,BIO.ScreeningDate
    ,PHA.PHADate

FROM    Registration RL 
        LEFT OUTER JOIN PHADetails PHA
            on RL.UserName = PHA.Username
        FULL OUTER JOIN Biometrics BIO
            on RL.UserName = BIO.Username 
...

To use your Excel data, see Online demo,

SELECT DISTINCT
     RL.UserName
    ,RL.Firstname
    ,RL.Lastname
    ,COALESCE(BIO.Year, PHA.Year) As Year
    ,BIO.One    
    ,PHA.Four


FROM    UserTable RL 
        LEFT OUTER JOIN AddInfoTable PHA
            ON RL.UserName = PHA.Username
        FULL OUTER JOIN BaseInfoTable BIO
            ON RL.UserName = BIO.Username
ORDER BY 
    COALESCE(BIO.Year, PHA.Year) DESC;