How to bring records of a LEFT JOIN even if it does not obey WHERE?

You almost only need to remove Where because it does the final filter, ie only the lines that have the Where filter will resume, if you use AND soon after ON LEFT JOIN will be returned all of tabela_A and tabela_B conditions.

  

ON a.cod = b.cod_tabela_A       and year = 2016 AND month = 2

declare @tabela_A table
(
    cod int,
    nome varchar(100)
)

declare @tabela_B table
(
    cod_tabela_A     int,
    ano int,
    mes int,
    valor int
)

insert into @tabela_B values
(1        ,       2016   , 1  , 100  ),   
(1     ,          2016  ,  2  , 115),
(2   ,            2016  ,  1  , 90)

insert into @tabela_A values
(1 ,      'stack'),
(2 ,      'overflow'),
(3 ,     ' stackoverflow')


SELECT * FROM @tabela_A a
LEFT JOIN @tabela_B b ON a.cod = b.cod_tabela_A
and ano = 2016 AND mes = 2

I think the code below solves your problem, just a tip, I know you want to return all fields of both tables but sometimes two tables have some field with the same name and ends up giving duplicate field error. I've been working with the database for years and I see this happen a lot. Here's what you need, put using the standardization that I use in the company.

 SELECT A.cod AS codA, A.nome AS nomeA, 
        B.cod AS codB, B.codigo_tabela_a AS cod_tb_A, 
        B.ano AS anoB, B.mes AS mesB, 
        B.valor AS valB
 FROM @tabela_a A
 LEFT JOIN tabela_b B ON A.cod = B.cod_tabela_a AND B.ano = 2016 AND B.mes = 2

Hello, you can use IF without the WHERE. You will get all the results, where SE meet the criteria (b.ano = 2016 AND b.mes = 2) returns the original value,
 otherwise NULL .

* I separated with blank lines for ease of understanding.

SELECT a.cod, a.name,
IF ( b.ano = 2016 AND b.mes = 2, b.cod_tabela_A, NULL) AS cod_tabela_A
IF b.ano = 2016 AND b.mes = 2, b.ano, NULL) AS year ,
IF month , b.ano = 2016 AND b.mes = 2, b.mes, NULL) AS month b.mes = 2, b.value, NULL) AS value
FROM table_A to
LEFT JOIN table_B b ON a.cod = b.cod_table_A

Where I put NULL you can use another value like '' to leave it blank. IF (b.ano = 2016 AND b.mes = 2, b.ano, '')

I did it that way and it worked, but I do not know if it would suit you.

declare @retorna_dado int,
@ano int,
@mes int
set @ano = 2016
set @mes = 2
set @retorna_dado = (SELECT COUNT(*) FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A
WHERE ano = @ano AND mes = @mes)

print @retorna_dado

if (@retorna_dado > 0)

SELECT * FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A
WHERE ano = @ano AND mes = @mes

else
SELECT * FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A

If you declare the year 2016 and month 02 (or any one that exists in the table) it will bring with the where, if it does not exist it will ignore the where

See if it helps