Skip to main content
added 250 characters in body
Source Link
thaJeztah
  • 221
  • 2
  • 6

I think this part:

CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome

Can be replaced with:

CONCAT(a.nome,  ' (' || b.nome || ')') AS nome

If b.nome is NULL, then "' (' || b.nome || ')'" will also be NULL (concatenating strings using '||' with NULL values will result in NULL. CONCAT() ignore null values, so will return only a.none in this case

In other words: if b.nome is present, it will output "a.nome (b.nome)", otherwise "a.nome"

You can test it yourself, here's my sqlfiddle:

http://sqlfiddle.com/#!1/d41d8/744/0

Also, you can try to use a subselect to prevent having to repeat this CONCAT;

http://sqlfiddle.com/#!1/a9571/7/0

I haven checked if this improves performance, but it reduces the amount of repeated code and may be better maintainable

I think this part:

CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome

Can be replaced with:

CONCAT(a.nome,  ' (' || b.nome || ')') AS nome

If b.nome is NULL, then "' (' || b.nome || ')'" will also be NULL (concatenating strings using '||' with NULL values will result in NULL. CONCAT() ignore null values, so will return only a.none in this case

In other words: if b.nome is present, it will output "a.nome (b.nome)", otherwise "a.nome"

You can test it yourself, here's my sqlfiddle:

http://sqlfiddle.com/#!1/d41d8/744/0

I think this part:

CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome

Can be replaced with:

CONCAT(a.nome,  ' (' || b.nome || ')') AS nome

If b.nome is NULL, then "' (' || b.nome || ')'" will also be NULL (concatenating strings using '||' with NULL values will result in NULL. CONCAT() ignore null values, so will return only a.none in this case

In other words: if b.nome is present, it will output "a.nome (b.nome)", otherwise "a.nome"

You can test it yourself, here's my sqlfiddle:

http://sqlfiddle.com/#!1/d41d8/744/0

Also, you can try to use a subselect to prevent having to repeat this CONCAT;

http://sqlfiddle.com/#!1/a9571/7/0

I haven checked if this improves performance, but it reduces the amount of repeated code and may be better maintainable

Source Link
thaJeztah
  • 221
  • 2
  • 6

I think this part:

CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome

Can be replaced with:

CONCAT(a.nome,  ' (' || b.nome || ')') AS nome

If b.nome is NULL, then "' (' || b.nome || ')'" will also be NULL (concatenating strings using '||' with NULL values will result in NULL. CONCAT() ignore null values, so will return only a.none in this case

In other words: if b.nome is present, it will output "a.nome (b.nome)", otherwise "a.nome"

You can test it yourself, here's my sqlfiddle:

http://sqlfiddle.com/#!1/d41d8/744/0