JOIN not work with where

Question

I have this code :

select
  B.plc_nomeConta, B.plc_classificador, B.plc_id,
  A.cap_idPlanoContasFin, SUM(A.cap_valorfatura) as Total     
from
  tbl_PlanoFinanceiro B
  left outer join erp_contaspagar A on B.plc_id = A.cap_idPlanoContasFin
   /*  where A.cap_idEmpresa like 2*/
group by
  B.plc_nomeConta,
  B.plc_classificador,
  B.plc_id,
  A.cap_idPlanoContasFin

This code returns 185 Lines,

(-) COFINS     10.01.01.01.004  330  330   971090,97
(-) ICMS       10.01.01.01.002  328  328   1378407,11
(-) IMPOSTOS   10.01.00.00.000  324  NULL  NULL
(-) IMPOSTOS   10.01.01.00.000  325  NULL  NULL
(-) IMPOSTOS   10.01.01.01.000  326  NULL  NULL
(-) ISS        10.01.01.01.001  327  327   1000960,59
(-) PIS        10.01.01.01.003  329  329   240600,27

but when I uncomment the where /* where A.cap_idEmpresa like 2*/, returns only the lines where A.cap_idPlanoContasFin is not null, In need ever B.plc_nomeConta, B.plc_classificador, B.plc_id appears.

Try `AND` instead of `WHERE`. – Aaron Bertrand Nov 13 '12 at 19:34 — Aaron Bertrand, Nov 13 '12 at 19:34

score 4 · Accepted Answer · answered Nov 13 '12 at 19:31

4

Your WHERE filter is converting the LEFT OUTER JOIN to an INNER JOIN.

Essentially you are saying "Show me all records on the left, and only records on the right that match AND have a cap_idEmpresa value of 2".

This means you are only showing matching records, which is an INNER JOIN -- any unmatched records cannot have a value of 2 in that field.

To correct you either need to account for null:

WHERE (A.cap_idEmpresa like 2 OR A.cap_idEmpresa IS NULL)

or refine your requirements.

answered Nov 13 '12 at 19:31

JNK

63,321
15
122
138

1

Alternatively, you could move `A.cap_idEmpresa like 2` to be part of the join. – cadrell0 Nov 13 '12 at 19:34
@cadrell0 Adding a value check to an `OUTER JOIN` will change the logic of the `JOIN`, though. – JNK Nov 13 '12 at 19:34
1

@JNK to be fair, I can't properly interpret the actual requirements. I can't even blame it on being a Monday. – Aaron Bertrand Nov 13 '12 at 19:36
@JNK The results set produced either way should be identical. I'm not sure which will have the more efficient execution plan, if that is what you mean. – cadrell0 Nov 13 '12 at 19:38
@cadrell0 they are semantically different: http://stackoverflow.com/questions/2509987/which-sql-query-is-faster-filter-on-join-criteria-or-where-clause – JNK Nov 13 '12 at 19:39
1

@JNK I think they are semantically different if you do a direct swap. The way you've structured your WHERE clause with the OR makes it semantically equivalent to making the conditional part of the join. – Aaron Bertrand Nov 13 '12 at 19:52
I have solve with JNK and cadrell0 , making the A.Cap_idempresa like 2 being part of the JOin , thanks by the here the code – alejandro carnero Nov 14 '12 at 17:18

score 0 · Answer 2 · answered Nov 14 '12 at 17:24

I have solve with JNK and cadrell0 , making the A.Cap_idempresa like 2 being part of the JOin , thanks by the here the code

        alter proc Ntrimestre (@emp integer,@inicio datetime,@fim datetime) as
        select  B.plc_nomeConta, B.plc_classificador ,B.plc_id ,
        A.cap_idPlanoContasFin, SUM (A.cap_valorfatura) as Total
        from tbl_PlanoFinanceiro B
        left outer join erp_contaspagar A on B.plc_id = A.cap_idPlanoContasFin 
        and   A.cap_idEmpresa like @emp
        and A.cap_vencfatura <= convert(datetime,@fim,1) 
        and cap_vencfatura >= convert(datetime,@inicio,1) 
        group by B.plc_nomeConta, B.plc_classificador ,B.plc_id, A.cap_idPlanoContasFin
       order by B.plc_classificador

The other way with where :

       alter proc NtrimestreMzFSant (@inicio datetime,@fim datetime) as
       select  B.plc_nomeConta, B.plc_classificador ,B.plc_id ,
        A.cap_idPlanoContasFin, SUM (A.cap_valorfatura) as Total
       from tbl_PlanoFinanceiro B
       left outer join erp_contaspagar A on B.plc_id = A.cap_idPlanoContasFin  
        and A.cap_vencfatura <= convert(datetime,@fim,1) 
         and A.cap_vencfatura >= convert(datetime,@inicio,1) 
        where B.plc_tipo <> 'Sintética' and A.cap_idEmpresa =2 or A.cap_idEmpresa=2234 
        group by B.plc_nomeConta, B.plc_classificador ,B.plc_id, A.cap_idPlanoContasFin
        order by B.plc_classificador

Thanks every body by the help

JOIN not work with where

2 Answers2