Is it possible to use if Else in Mysql queries?

Question

I’ve been searching the internet and hearing rumors that it is possible to use if else in Mysql queries but saw nothing concrete about it. Is it even possible? If so, how?

I want to make a query in three distinct tables in search of the column name I have the following query as an example

SELECT `PAIS`.nome, `FILHOS`.nome, `NETOS`.nome FROM `PAIS`, `FILHOS`, `NETOS` WHERE `PAIS`.nome LIKE '%MATILDE%' OR `FILHOS`.nome LIKE '%MATILDE%' OR `NETOS`.nome LIKE '%MATILDE%'

That one query will make a query in the three tables PAIS, FILHOS, NETOS looking for '%MATILDE%' in the name field

Now I want to do how in this example but with if

SE encontrar '%MATILDE%' na tabela NETOS CONCATENAR
CONCAT(' É FILHA DE ', FILHOS.nome),
CONCAT(' E NETA DE ', PAIS.nome)

SE encontrar '%MATILDE%' na tabela PAIS CONCATENAR
CONCAT(' É MAE DE ', FILHOS.nome),
CONCAT(' E AVO DE ', NETOS.nome)

I want to implement something more or less like this.

Answer 1

In the type of query you intend to do, you can resolve with a subquery:

set @nome:='MATILDE';

SELECT CONCAT(' É MÃE DE ', f.nome) as filha,
       CONCAT(' É FILHA DE ', p.nome) as mae,
       CONCAT(' É AVÓ DE ',(SELECT nome from PAIS where nome like '%@nome%')) as neta,
       CONCAT(' É NETA DE ',p.nome) as avo,
    FROM `PAIS` p, 
         `FILHOS` f,
         `NETOS` n 
  WHERE `p`.nome LIKE '%@nome%'
     OR `f`.nome LIKE '%@nome%' 
     OR `n`.nome LIKE '%@nome%';

And use the condition IF to ignore the concatenation, since there may not always be daughters, granddaughters, mothers and grandparents in the same database, you can make a filter:

   SELECT  IF(f.nome IS NOT NULL, CONCAT(' É MÃE DE ', f.nome), null) as filha,
           IF(p.nome IS NOT NULL, CONCAT(' É FILHA DE ', p.nome), null) as mae,
           IF((SELECT nome from NETOS where nome like '%@nome%') IS NOT NULL, CONCAT(' É AVÓ DE ',(SELECT nome from NETOS where nome like '%@nome%'), null) as neta,
           IF(p.nome IS NOT NULL, CONCAT(' É NETA DE ',p.nome),null) as avo,
        FROM `PAIS` p, 
             `FILHOS` f,
             `NETOS` n 
      WHERE `p`.nome LIKE '%@nome%'
         OR `f`.nome LIKE '%@nome%' 
         OR `n`.nome LIKE '%@nome%';

Obs: your question was confused, because in the relationship you present, it was not clear what differentiates the "granddaughters" in the relationship between fathers and daughters. For granddaughters can be mothers as well as daughters. Even if I try the same last name, a mother may come as her own granddaughter if she only considers the name relationship.

Answer 2

In SQL it is possible to use CASE ELSE

CASE WHEN aaa = bbb THEN xxx ELSE zzzz END

Example:

SELECT a, b, c, (CASE WHEN aaa = bbb THEN xxx ELSE zzzz END) AS d
FROM table

It is compatible with SQL 92, works on SQL SERVER and Mysql

Answer 3

This is a function almost equal to what exists in Excel, or as we are more accustomed in programming, works analogous to a conditional operator. The ELSE is consequence because the first argument of the function is the condition, the second is the expression it must return if the condition is true and the third argument is what will return if the condition is false, bearing what "would be executed" in the ELSE.

Documentation.

Then it is not used as flow control effectively, but only as part of a value calculation (it is like the conditional operator). You can only do simple things.

In Bacco’s commentary has real-use example.

There even is the IF/ELSE for flow control, but not to use in SQL queries, there is to program functions and procedures.

There you can see that you can use one CASE also that it works in a similar way, only that it can have several options, that is, for each value found in the main expression (probably a column) there will be the return of another corresponding value. it is possible to use a ELSE in it also.

So I wouldn’t call it a rumor, it was information passed on because someone was giving greater grounds and references. Whenever possible consult the official documentation. Or ask here :)