JOIN return with NULL value

Question

I have a table with COMPANY and another table with AFFILIATE, in another table I have the link of USER by COMPANY and AFFILIATE ( that is the TB001UXE, if the affiliate is not in the table should consider all affiliates of the company).

The problem that when I do INNER JOIN the field this NULL does not appear the corresponding branches.

SELECT uxe.ca000coduxe, uxe.ce_ca000codusu, uxe.ce_ca000codemp, uxe.ce_ca000codfil, fil.ca000codfil, fil.ce_ca000codemp
FROM sisse.tb001uxe AS uxe
JOIN sisse.tb004fil AS fil
ON
uxe.ce_ca000codemp = fil.ce_ca000codemp
order by ca000coduxe;

Example above the field CE_CA000CODFIL of the third line this null, being the company 11 (ce_ca000codemp) and in the second line was linked to affiliate 1 (ce_ca000codfil) affiliate 3 (ca000codfil) up to there all right, the problem is when I do the mooring between the branches.

Example: when I tie the branches to company 11 (ce_ca000codemp - the third line of the top image) no longer appears in the query.

SELECT uxe.ca000coduxe, uxe.ce_ca000codusu, uxe.ce_ca000codemp, uxe.ce_ca000codfil, fil.ca000codfil, fil.ce_ca000codemp
FROM sisse.tb001uxe as uxe
JOIN sisse.tb004fil as fil
ON
uxe.ce_ca000codemp = fil.ce_ca000codemp and
uxe.ce_ca000codfil = fil.ca000codfil
order by ca000coduxe;

I tried to use LEFT JOIN and RIGHT JOIN and does not bring the expected result.

Answer 1

From what I understand the result you want to try:

SELECT uxe.ca000coduxe, uxe.ce_ca000codusu, uxe.ce_ca000codemp, uxe.ce_ca000codfil, fil.ca000codfil, fil.ce_ca000codemp
FROM sisse.tb001uxe as uxe 
CROSS JOIN sisse.tb004fil as fil
ON (uxe.ce_ca000codemp = fil.ce_ca000codemp AND ((uxe.ce_ca000codfil = fil.ca000codfil) OR (uxe.ce_ca000codfil IS NULL))
order by ca000coduxe;