Resumo : Neste tutorial, você aprenderá como usar a COALESCE
função MySQL para substituir valores NULL.
Introdução à função MySQL COALESCE
A COALESCE
função recebe vários argumentos e retorna o primeiro argumento não NULO. Caso todos os argumentos sejam NULL, a COALESCE
função retorna NULL.
O seguinte ilustra a COALESCE
sintaxe da função:
COALESCE(value1,value2,...);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Por exemplo:
SELECT COALESCE(NULL, 0); -- 0
SELECT COALESCE(NULL, NULL); -- NULL;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Exemplos de funções COALESCE do MySQL
Consulte a customers
tabela a seguir no banco de dados de exemplo :
A consulta a seguir retorna o nome do cliente, cidade, estado e país de todos os clientes na customers
tabela.
SELECT
customerName, city, state, country
FROM
customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como você pode ver, a coluna de estado possui valores NULL porque algumas dessas informações não são aplicáveis ao país de alguns clientes.
Para substituir o valor NULL no conjunto de resultados, você pode usar a COALESCE
função da seguinte forma:
SELECT
customerName, city, COALESCE(state, 'N/A'), country
FROM
customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo, se o valor na coluna de estado for NULL, a COALESCE
função irá substituí-lo pela N/A
string. Caso contrário, retorna o valor da coluna de estado.
Outro exemplo típico de utilização da COALESCE
função é substituir o valor de uma coluna por outra quando a primeira for NULL.
Suponha que você tenha uma articles
tabela com a seguinte estrutura:
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
excerpt TEXT,
body TEXT NOT NULL,
published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Vamos inserir alguns dados na articles
tabela.
INSERT INTO articles(title,excerpt,body)
VALUES('MySQL COALESCE Tutorial','This tutorial is about MySQL COALESCE function', 'all about COALESCE function'),
('MySQL 8.0 New Features',null, 'The following is a list of new features in MySQL 8.0');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Imagine que você tenha que exibir artigos em uma página de visão geral onde cada artigo contém o título, o especialista e a data de publicação (e também o link leia mais para a página do artigo). A primeira tarefa que você precisa fazer é consultar esses dados da articles
tabela:
SELECT
id, title, excerpt, published_at
FROM
articles;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como você pode ver, o artigo com id 2 não contém o trecho, o que não é bom para exibição.
Uma solução típica é obter o primeiro número de caracteres do body
artigo para exibição como trecho. É por isso que a COALESCE
função entra em jogo.
SELECT
id, title, COALESCE(excerpt, LEFT(body, 150)), published_at
FROM
articles;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo, se o valor na coluna de trecho for NULL, a COALESCE
função retornará os primeiros 150 caracteres do conteúdo da body
coluna.
Expressão MySQL COALESCE e CASE
Além de usar a COALESCE
função, você pode usar a CASE
expressão para obter o mesmo efeito.
A consulta a seguir usa a CASE
expressão para obter o mesmo resultado do exemplo acima:
SELECT
id,
title,
(CASE
WHEN excerpt IS NULL THEN LEFT(body, 150)
ELSE excerpt
END) AS excerpt,
published_at
FROM
articles;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo, a CASE
expressão é mais longa do que usar a COALESCE
função.
COALESCE vs.
A IFNULL
função recebe dois argumentos e retorna o primeiro argumento se não for NULL, caso contrário, retorna o segundo argumento.
A IFNULL
função funciona muito bem com dois argumentos, enquanto a COALESCE
função funciona com n
argumentos. Caso o número de argumentos seja dois, ambas as funções são iguais.
Neste tutorial, você aprendeu como usar a função MySQL COALESCE para substituir valores NULL.