sql - FULL OUTER JOIN is not returning content of all tables

Question

I have 3 tables as mentioned below.

TABLES

BUG

Id | Name | Other information ...
1  | Bug1 | ...
2  | Bug2 | ...
3  | Bug3 | ...
4  | Bug4 | ...
5  | Bug5 | ...

LINK

Id  | Test_id | Bug_id
100 | 1000    | 4
101 | 1100    | 2
102 | 1200    | 2
103 | 1200    | 5

TEST

Id   | Name | Other information ...
1000 | TC1  | ...
1100 | TC2  | ...
1200 | TC3  | ...
1300 | TC4  | ...
1400 | TC5  | ...
1500 | TC6  | ...

REQUEST

SELECT Bug.Id, Bug.Name, Test.Id, Test.Name
FROM Test FULL OUTER JOIN Link ON Link.Test_id = Test.Id
FULL OUTER JOIN Bug ON Link.Bug_id = Bug.Id
INNER JOIN Test_Detail ON Test_Detail.Test_id = Test.Id
INNER JOIN Release ON Release.Id = Test.Release_id
INNER JOIN Cycle ON Cycle.Release_id = Release.Id

However, this is not providing the expected result ...

EXPECTED RESULT

• if there is a link, I would like to get information about the bug and the test
• if there is a bug without link, I would like to get information about the bug
• if there is a test without link, I would like to get information about the test

Result

BugId | BugName | TestId | TestName
1     | Bug1    |
2     | Bug2    | 1100   | TC2
2     | Bug2    | 1200   | TC3
3     | Bug3    |
4     | Bug4    | 1000   | TC1
5     | Bug5    | 1200   | TC2
      |         | 1300   | TC4
      |         | 1400   | TC5
      |         | 1500   | TC6
    

CONCLUSION

FULL OUTER JOIN is not the correct join to use ? Can you please help to understand why it is not working as expected?

Thank you

EDIT 1

Result based on Charlieface answer.

I'm getting the bug, the test and the link. But I need only the link if it exists. If there is no link, I need the test and the bug detail. As mentioned on Expected Result section above.

BugId | BugName | TestId | TestName
      |         | 1100   | TC2
      |         | 1200   | TC3
2     | Bug2    |        | 
2     | Bug2    | 1100   | TC2
2     | Bug2    | 1200   | TC3

EDIT 2

Full request

(TESTCYCL is TEST in this post)

SELECT DISTINCT
B.BG_BUG_ID
, B.BG_STATUS
, B.BG_SUMMARY
, B.BG_USER_TEMPLATE_13
, B.BG_SEVERITY
, B.BG_PRIORITY
, B.BG_USER_TEMPLATE_10
, B.BG_USER_TEMPLATE_08
, R.REL_NAME
, RC2.RCYC_NAME
, B.BG_DETECTION_DATE
, B.BG_RESPONSIBLE
, B.BG_USER_TEMPLATE_03
, B.BG_USER_03
, B.BG_CLOSING_DATE
, B.BG_USER_TEMPLATE_19
, B.BG_USER_TEMPLATE_18
, B.BG_DETECTED_BY
, TC.TC_TESTCYCL_ID
, TC.TC_TEST_ID
, T.TS_NAME
, TC.TC_STATUS
, T.TS_USER_TEMPLATE_05
, CF.CF_ITEM_NAME
, C.CY_CYCLE
, RC1.RCYC_NAME
, TC.TC_USER_TEMPLATE_06
, TC.TC_USER_TEMPLATE_05
, TC.TC_USER_TEMPLATE_08
FROM TESTCYCL AS TC
INNER JOIN RELEASE_CYCLES AS RC1 ON TC.TC_ASSIGN_RCYC = RC1.RCYC_ID
INNER JOIN TEST AS T ON TC.TC_TEST_ID = T.TS_TEST_ID
INNER JOIN CYCLE AS C ON TC.TC_CYCLE_ID = C.CY_CYCLE_ID
INNER JOIN CYCL_FOLD AS CF ON C.CY_FOLDER_ID = CF.CF_ITEM_ID
FULL OUTER JOIN LINK AS L ON L.LN_ENTITY_ID = TC.TC_TESTCYCL_ID
FULL OUTER JOIN BUG AS B
INNER JOIN RELEASE_CYCLES AS RC2 ON B.BG_DETECTED_IN_RCYC = RC2.RCYC_ID
INNER JOIN RELEASES AS R ON B.BG_DETECTED_IN_REL = R.REL_ID
ON L.LN_BUG_ID = B.BG_BUG_ID

Answer 1

You need to reorder your joins.

Put all inner joins relating to one table together with that table.:

SELECT Bug.Id, Bug.Name, Test.Id, Test.Name
FROM Test
INNER JOIN Test_Detail ON Test_Detail.Test_id = Test.Id
INNER JOIN Release ON Release.Id = Test.Release_id
INNER JOIN Cycle ON Cycle.Release_id = Release.Id
FULL OUTER JOIN Link ON Link.Test_id = Test.Id
FULL OUTER JOIN Bug ON Link.Bug_id = Bug.Id

Let us assume you want to join something onto Bug also. For this, you need to nest the ON conditions:

SELECT Bug.Id, Bug.Name, Test.Id, Test.Name
FROM Test
INNER JOIN Test_Detail ON Test_Detail.Test_id = Test.Id
INNER JOIN Release ON Release.Id = Test.Release_id
INNER JOIN Cycle ON Cycle.Release_id = Release.Id
FULL OUTER JOIN Link ON Link.Test_id = Test.Id
FULL OUTER JOIN Bug
      INNER JOIN Bug_Detail bd ON bd.Bug_id = Bug.id
  ON Link.Bug_id = Bug.Id

Note that, contrary to popular opinion, parenthesis () do not make a difference here, although they are useful visually. It's the order of the ON clauses that matter: Bug_detail inner join is nested inside the full join of Bug