BE-TWEEN or not BE-TWEEN;
that is the question.
1. Introdução
BETWEEN, segundo a documentação da Microsoft, é um operador lógico cuja função é definir um intervalo fechado a ser testado. Por exemplo, o predicado
significa verificar se o valor da coluna Peso está entre 75 (inclusive) e 80 (inclusive). Detalhando melhor,
Sendo assim, qual a diferença entre as formas A e B acima?
2. Comparando ambos
Para dissipar essa dúvida vou (mais uma vez) apelar para o plano de execução, mesmo procedimento que fiz em outros artigos da série tira-teima, mas nos atendo ao conteúdo do predicado do operador de leitura. Uma tabela com medidas antropométricas, MedidaAM, será utilizada neste artigo. Esta tabela contém o código da pessoa, o peso (em quilogramas), a altura (em centímetros) e a data e hora em que foi realizada a medição.
O código sql para a criação da tabela é o seguinte:
-- código #2.1
-- © José Diz / Porto SQL
CREATE TABLE MedidaAM (
Pessoa integer not null,
Peso decimal (5,1) not null,
Altura tinyint not null,
Anotado datetime not null
);
E a carga da tabela, com poucas linhas somente para a demonstração, é a seguinte:
-- código #2.2
-- © José Diz / Porto SQL
INSERT into MedidaAM (Pessoa, Peso, Altura, Anotado)
values (512, 50.2, 155, '20120215'),
(25, 79, 165, '20120312 08:15'),
(18, 85, 182, '20120422'),
(212, 77, 190, '20120314 8:18'),
(181, 78.5, 171, '20120315');
Observe que para algumas pessoas foram anotados data e horário da medição, enquanto que para outras somente a data:
2.1. X between A and B
O primeiro teste será utilizando o operador lógico BETWEEN no predicado.
-- código #2.3
-- © José Diz / Porto SQL
SELECT Pessoa, Peso as [Peso (Kg)],
Altura as [Altura (cm)],
cast ((Peso / power ((Altura / 100.0), 2)) as decimal(4,1)) as IMC
from MedidaAM
where Peso between 75.0 and 80.0;
Observe que na cláusula WHERE está o predicado que verifica se o valor da coluna Peso está entre 75 e 80. Como a coluna Peso está declarada como real, então utilizou-se “.0” nos valores de 75 e 80; desta forma evita-se a conversão automática de tipo de dados.
O resultado da execução do código #2.3 é o seguinte:
Ao inspecionar o plano de execução, especificamente as informações do operador de leitura, sobre o predicado definido pelo compilador sql temos:
Sobre o predicado (Peso >= @1 and Peso <= @2), duas observações:
(1) o compilador implementou automaticamente parâmetros no lugar dos valores: 75.0 foi substituído por @1 e 80.0 por @2;
(2) não há, internamente, operador lógico BETWEEN. O que o compilador sql fez foi substituir
PESO between 75.0 and 80.0
por
(PESO >= 75.0 and PESO <= 80.0)
Sobre a observação (1), nos testes deste artigo a configuração de PARAMETERIZATION do banco de dados estava SIMPLE e ocorreu a parametrização automática da consulta sql, provavelmente pela simplicidade do código sql. Detalhes em Simple parameterization.
2.2. (X >= A and X <= B)
Passando agora para o segundo teste, que a princípio nem mais seria necessário.
-- código #2.4
-- © José Diz / Porto SQL
SELECT Pessoa, Peso as [Peso (Kg)],
Altura as [Altura (cm)],
cast ((Peso / power ((Altura / 100.0), 2)) as decimal(4,1)) as IMC
from MedidaAM
where (Peso >= 75.0 and Peso 80.0);
O resultado da execução do código #2.4 é o mesmo do anterior.
Ao inspecionar o plano de execução, especificamente as informações do operador de leitura, sobre o predicado definido pelo compilador sql temos:
Idêntico ao do código #2.3, que utiliza BETWEEN.
2.3. Conclusão
Considerando-se os testes efetuados, percebe-se que internamente não existe o operador lógico BETWEEN e o compilador sql transforma essa construção sql em algo como “(X >= A and X <= B)”.
Além dos testes mencionados neste artigo, fiz vários outros com situações complexas na comparação e sempre analisando o que o compilador sql colocava no predicado. Em um desses testes observei que o compilador inverteu a sequência: colocou primeiro o “X <= B” e após o “X >= A”. Não aprofundei nesse caso mas anotei, para futura averiguação:
Há alguma diferença entre "(X >= A and X <= B)" e "(X <= B and X >= A)" ?
Após escrever este artigo, e estando já publicado, resolvi pesquisar na web o que havia de artigo ou perguntas em fóruns sobre o assunto. Não encontrei nada diferente do que consta neste artigo, exceto que em uma das respostas em um fórum havia a seguinte pergunta:
Did anybody look at differences when is in the other side of the clause. SELECT * FROM table WHERE @date BETWEEN someCol1 AND someCol2 SELECT * FROM table WHERE someCol1 >= @date AND someCol2 <= @date
3. Mas, contudo, todavia…
Mas há relatos de que o BETWEEN não funciona quando comparando datas; será mesmo?
Ainda utilizando a tabela MedidaAM, vamos construir consulta sql que retorne os dados que foram coletados no período de um mês, iniciando em 15 de Fevereiro de 2012.
Para refrescar a memória e facilitar a análise dos resultados, o conteúdo da tabela MedidaAM é o seguinte:
Dito isto, vamos ao código sql:
-- código #3.1 v2
-- © José Diz / Porto SQL
--
declare @DataInicial datetime, @DataFinal datetime;
set @DataInicial= convert (datetime, '15/2/2012', 103);
set @DataFinal= convert (datetime, '14/3/2012', 103);
--
SELECT Pessoa, Peso as [Peso (Kg)], Altura as [Altura (cm)],
convert (char(10), Anotado, 103) as [Anotado em],
cast ((Peso / power ((Altura / 100.0), 2)) as decimal(4,1)) as IMC
from MedidaAM
where Anotado between @DataInicial and @DataFinal;
Temos então como resultado do código #3.1 a relação a seguir:
Ops! Está faltando alguma coisa… Os dados da pessoa cujo código é 212 foram anotados no dia 14/3/2012; logo, deveriam constar do relatório. Qual o motivo de não aparecer, se o filtro delimitou o período de 15/2 a 14/3?
Indo direto à resposta, a causa é que o filtro foi entre “15/2/2012 0:00” e “14/3/2012 0:00”, pois as variáveis @DataInicial e @DataFinal estão declaradas como datetime e, por isso, possuem a parte de horário que, quando não informado, é preenchido com zeros.
Uma solução seria definir @DataFinal com “14/3/2012 23:59:59”, quando então temos o seguinte resultado:
Apareceu a pessoa de código 212. Então… o código #3.1 agora está correto? Infelizmente, ainda não. Suponha que a data e horário de anotação dos dados da pessoa 212 fosse “14/3/2012 23:59:59.007”, as informações dessa pessoa apareceriam no relatório? NÃO, pois “23:59:59.007” é após (maior do que) “23:59:59.000”.
Vamos então definir o valor de @DataFinal com “14/3/2012 23:59:59.999”, e não se fala mais nisso! Eis o resultado:
A pessoa de código 181 não poderia estar nesse relatório, que é do período de 15/2 a 14/3. Ops! Caímos agora no caso do artigo “Para onde foi o 23:59:59.999?!”, onde se encontra a solução que (atualmente) acho confiável para comparar períodos de datas quando a coluna contém data e hora.
Ei, o texto aqui já acabou, clica logo no link acima…
Porto SQL 