Como lidar com os SQL NULLs: Função COALESCE

É inevitável que alguns dados no banco de dados não tenham valor o que em SQL é representado pela palavra-chave NULL. "Nenhum valor" aqui é diferente de zero, falso, ou uma string vazia (mas com exceções! Em Oracle banco de dados, NULL é o mesmo que um fio de comprimento zero). Durante o jogo com o banco de dados, o tratamento do NULLs está se tornando cada vez mais problemático, portanto MySQL padrão fornece ajuda com algumas das funções, como COALESCE.

Vamos apresentar a função COALESCE() função:

Definição

COALESCE A função retorna um primeiro argumento não NULL da lista de argumentos aprovados. Sua sintaxe é a seguinte:

COALESCE(x, y, … , n)

O que adicionalmente é importante, isso:

• São necessários pelo menos dois argumentos.
• As expressões na função COALESCE devem ser avaliadas para o mesmo tipo de dados (por exemplo, a instrução SQL SELECT COALESCE (1, 'aa', NULL, 23); irá produzir um erro).
• Se todos os argumentos listados avaliam para NULL, a função também retorna NULL.

COALESCE é essencialmente uma declaração resumida CASE apresentada abaixo:

CASE
WHEN x IS NOT NULL THEN x
WHEN y IS NOT NULL THEN y
WHEN ... IS NOT NULL THEN …
ELSE n
END

COALESCE - exemplos de uso

Agora, dê uma olhada no protótipo apresentado abaixo do modelo de banco de dados da universidade. Mostrarei alguns exemplos que requerem a função COALESCE que se refere às tabelas deste modelo.

Usando COALESCE com concatenação de valores

Dê uma olhada na tabela student a partir do modelo de banco de dados universitário introduzido:

Pode acontecer, que a pessoa em particular não possa ter o nome do meio, portanto, alguns registros na coluna middle_name podem ser NULLs como mostram os dados do exemplo abaixo.

Vamos tentar concatenar o primeiro nome, o nome do meio e o sobrenome do aluno. Este funciona no PostgreSQL, Oracle.

SELECT
  first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM student;

A tabela de resultados contém uma coluna full_name com as cordas concatenadas.

Note que apenas um registro tem um nome completo adequado. Os demais são NULLs, porque o nome do meio também era NULL (enquanto concatenando as cordas com o valor NULL, o resultado também é NULL).

Tal resultado não parece muito agradável. O resultado não mostra o nome completo dos alunos sem o nome do meio. Aqui o COALESCE() função vem com o resgate. Usando isto, podemos substituir os campos NULL por um fio vazio, por exemplo. Agora, a nova consulta parece ser a seguinte:

SELECT
  first_name || COALESCE(' ' || middle_name || ' ', ' ') || last_name AS full_name
FROM student;

O resultado agora está completo 🙂

MySQL, MS SQL Server usa para a função de concatenação CONCAT() ou '+' operador.

Da mesma forma, a consulta no MySQL ou MS SQL Server terá a seguinte aparência:

SELECT
  CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM student;

... e os registros que têm NULLs também terão :

Com a função COALESCE, os NULLs que aparecem na coluna middle_name serão substituídos.

SELECT
  CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name)
FROM student;

Usando o PostgreSQL COALESCE com funções agregadas

Bem, NULLs de fato pode parecer muito problemático. Vamos imaginar uma situação diferente. Quero saber qual é a nota média para o curso. Parece muito fácil, certo? Mas, espere, espere... mesmo que esta seja uma simples pergunta, podemos encontrar alguns inconvenientes. A questão diz respeito a algumas das funções agregadas do SQL, como SUM(), MAX() ou MIN().

EXEMPLO: Usando COALESCE com a função AVG

Vamos tentar um exemplo com a tabela student_courses.

Como você vê abaixo, os alunos que estão inscritos no curso de id 1 ainda não têm uma nota.

A consulta, que retorna a nota média para o curso, parece ser a seguinte:

SELECT
  course_instance_id,
  AVG(grade) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Para a função AVG(), os campos nãoNULL são somados e a soma é dividida pelo número de campos nãoNULL. Portanto, se todos os registros tiverem NULL na coluna de nota, a nota média será NULL também.

A tabela de resultados abaixo:

Neste caso, queremos inserir outro valor, como 0 (a nota pode ser um valor de 2 a 5, de modo que a média 0 pode indicar para nós que os alunos não têm notas)

COALESCE() vem com ajuda na substituição de um valor. A mesma consulta com COALESCE será:

SELECT
  course_instance_id,
  COALESCE(AVG(grade), 0) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

E agora podemos obter a tabela de resultados como:

EXEMPLO: Usando a função COALESCE com SUM()

Este aqui é um exemplo semelhante. Vamos assumir uma situação diferente. Queremos contar a quantidade total de horas de ausência para cada estudante. Dê uma olhada na parte selecionada do modelo:

Os dados da tabela student_course_attendance são os seguintes.

Enquanto os dados student_courses são:

Dê uma olhada de perto nos dados dessas tabelas. Como você vê, dois alunos estão registrados para o curso em particular, enquanto apenas um estava faltando às aulas.

A consulta, que calculará a soma do número total das horas de ausência para um course_id = 1 será para cada aluno:

SELECT
  student_id,
  SUM(absence_hours) AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

... e avalia em uma tabela de resultados:

A hora total de ausência foi avaliada para NULL, porque de fato, este aluno não perdeu nenhuma aula nesta instância do curso. Não havia nenhum registro para este aluno na tabela student_course_attendance, e a função SUM() retornou NULL. Podemos garantir a partir desta situação do curso usando COALESCE função.

A nova consulta é a seguinte:

SELECT
  student_id,
  COALESCE(SUM(absence_hours), 0)  AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

E o resultado é agora:

Abandonemos agora o modelo universitário e vejamos o exemplo diferente.

Usando COALESCE na criação das tabelas PIVOT

Este caso de uso será apresentado para o banco de dados PostgreSQL. Note que outros bancos de dados como Oracle e MS SQL Server têm suas funções equivalentes para a criação de tabelas pivot.

Veja a tabela abaixo. Este é o resultado de alguma consulta, que mostra qual é a venda total para cada marca no mês em particular.

Quero transpor as filas com colunas (criar uma tabela pivô). A tabela de resultados deve ter colunas: brand_id, jan, feb, ..., dec. Note que não em todos os meses foi vendido um produto de uma marca em particular. Vamos dar uma olhada no exemplo do Postgres.

SELECT brand_id, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec

FROM CROSSTAB (
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Após executar tal consulta, nós recebemos:

Infelizmente, a venda é registrada apenas em alguns meses e somente nesses campos, o número total de vendas é colado. Como você provavelmente esperava, o resto dos campos contém NULLs.

Para se livrar dos indesejados NULLs, podemos usar a função COALESCE, que quando necessário insere um 0 em vez de NULL. A consulta reparada parece ser a seguinte:

SELECT 
  brand_id,
  COALESCE(jan, 0),
  COALESCE(feb, 0),
  COALESCE(mar, 0),
  COALESCE(apr, 0),
  COALESCE(may, 0),
  COALESCE(jun, 0),
  COALESCE(jul, 0),
  COALESCE(aug, 0),
  COALESCE(sep, 0),
  COALESCE(oct, 0),
  COALESCE(nov, 0),
  COALESCE(dec, 0)
FROM CROSSTAB(
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Resumo

NULLs podem tornar a vida problemática. Se você ainda não experimentou o lado ruim dos valores que faltam, com certeza você se deparará com isso. É uma questão de tempo. Por enquanto, lembre-se da funçãoCOALESCE , que o ajudará a enfrentar os indesejados NULLs.