Where and Right/Left Join

Question

I have the following code in SQL Server, when I use my filter on INNER JOIN condition, right join works fine, but if I change its position and create a where, it stops to working, my question is every time that I use RIGHT or LEFT JOIN I can't use WHERE?

SELECT pcm.clacta,
       pcm.descta,
       pcm.ctared,
       SUM(mcp.vlrliq)
  FROM e501tcp tcp 
 INNER JOIN e501mcp mcp ON mcp.codemp = tcp.codemp
                       AND mcp.codfil = tcp.codfil
                       AND mcp.numtit = tcp.numtit
                       AND mcp.codtpt = tcp.codtpt
                       AND mcp.codfor = tcp.codfor 
 RIGHT JOIN (SELECT pcm.clacta,
                    pcm.descta,
                    pcm.ctared
               FROM e043pcm pcm 
              WHERE pcm.codmpc = 700) pcm ON pcm.ctared = tcp.ctafin    
 WHERE tcp.codfil = 1
   AND tcp.numtit = '1/01' 
 GROUP BY pcm.clacta,
          pcm.descta,
          pcm.ctared
 ORDER BY pcm.clacta;

Answer 1

I am not a fan of RIGHT JOIN. I think LEFT JOINs are much clearer (keep everything in the first table rather than the last table). In any case, your problem is that the WHERE clause is undoing the RIGHT JOIN.

I would write this as:

SELECT pcm.clacta,
       pcm.descta,
       pcm.ctared,
       SUM(mcp.vlrliq)
FROM e043pcm pcm LEFT JOIN
     e501tcp tcp 
     ON pcm.ctared = tcp.ctafin AND
        tcp.codfil = 1 AND
        tcp.numtit = '1/01' LEFT JOIN
     e501mcp mcp
     ON mcp.codemp = tcp.codemp AND
        mcp.codfil = tcp.codfil AND
        mcp.numtit = tcp.numtit AND
        mcp.codtpt = tcp.codtpt AND
        mcp.codfor = tcp.codfor 
 WHERE pcm.codmpc = 700
 GROUP BY pcm.clacta, pcm.descta, pcm.ctared
 ORDER BY pcm.clacta;

Notes:

• The condition on pcm can be in the WHERE clause.
• No subquery is needed for pcm.
• The original WHERE conditions are now in the ON clause.
• The order of the JOINs is switched around, with the second conditions switched to a LEFT JOIN.

Also, this logic is not exactly the same as yours. There are subtle differences based when there are matches in 2 of the 3 tables. I doubt that this makes a difference for the results that you want.