Joining 3 tables and retrieve all the records from all the tables

Answer 1

That's the definition of a FULL OUTER JOIN (which, when used, is almost invariably an indication of a poor design - I use a FULL OUTER JOIN about once a year).

Perhaps if you gave the results you were looking for?

I'm thinking UNION, GROUP BY and COALESCE.

Answer 2

If this is what you need:

TEAM LEVEL  Value1 Value2 Value3
A   1      1      NULL  NULL
B   1      NULL  1000  900

Then you can achieve that with the following:

SELECT [TEAM], [LEVEL], MAX(v1) Value1, MAX(v2) Value2, MAX(v3) Value3
FROM (
    SELECT [TEAM], [LEVEL], Value1 v1, NULL v2, NULL v3
    FROM TABLE1
    UNION
    SELECT [TEAM], [LEVEL], NULL, Value2, NULL
    FROM TABLE2
    UNION
    SELECT [TEAM], [LEVEL], NULL, NULL, Value3
    FROM TABLE3
) t0
GROUP BY [TEAM], [LEVEL]

and you can use as many tables as you need.

Answer 3

What you need is to add an additional match condition on the second join expression to allow it to match the second table's team/level values. I've also simplified the team/level column expressions using ISNULL:

SELECT DISTINCT 
    ISNULL(T0.[TEAM],ISNULL(T1.[TEAM],T2.[TEAM])) AS [TEAM],
    ISNULL(T0.[LEVEL],ISNULL(T1.[LEVEL],T2.[LEVEL])) AS [LEVEL],
    T0.[VALUE1], T1.[VALUE2], T2.[VALUE3]     
FROM TABLE1 T0
FULL JOIN TABLE2 T1 ON T0.[TEAM] = T1.[TEAM] AND T0.[LEVEL] = T1.[LEVEL] 
FULL JOIN TABLE3 T2 ON (T0.[TEAM] = T2.[TEAM] AND T0.[LEVEL] = T2.[LEVEL])
                    OR (T1.[TEAM] = T2.[TEAM] AND T1.[LEVEL] = T2.[LEVEL])

See? T1 didn't show on the same row as T2 because you never allowed that as a match possibility.