Switch between results

Question

Hello!

I have the following appointments:

SELECT id,
       nome,
       idade,
       data_nascimento,
       cpf
 FROM internos
 WHERE cpf = '12345678911'

and

SELECT id,
       nome,
       idade,
       data_nascimento,
       cpf
 FROM externos
 WHERE cpf = '12345678911'

I would like to know how to switch between the results:

How so?

If the date of birth of the result of the above query were null, bring the date of the below, the whole line:

I tried to do so:

SELECT id,
           nome,
           idade,
           IFNULL(data_nascimento, A.data_nascimento),
           cpf
     FROM internos
     WHERE cpf = '12345678911'

    UNION
   (
    SELECT id,
           nome,
           idade,
           data_nascimento,
           cpf
     FROM externos
     WHERE cpf = '12345678911'
 ) A

Because sometimes there is no data in one table but there is in another

At first the result comes like this:

 ____________________________________________________________
[ id  |  nome   |  idade  |   data_nascimento  |  cpf        ]  
[ 1   |  Carlos |   NULL  |     NULL           | 12345678911 ]
[ 15  |  carlos |   20    |     1999-04-01     | 12345678911 ]
--------------------------------------------------------------

In consultation I would like the consultation to prevail from below

 ____________________________________________________________
[ id  |  nome   |  idade  |   data_nascimento  |  cpf        ]  
[ 15  |  carlos |   20    |     1999-04-01     | 12345678911 ]
--------------------------------------------------------------

[EDIT]

If you have the complete data in the first result, the second is empty

Answer 1

For the explanation of the expected return, it would be more appropriate for you to use a right join instead of union, as it intends to bring primarily what has in the second table:

SELECT DISTINCT e.nome, e.idade, e.data_nascimento, e.cpf
FROM internos i
RIGHT JOIN externos e ON e.cpf = i.cpf
WHERE e.cpf = '12345678911'

Considering that the returned data will be equal in the two tables, the distinct will filter to select only one occurrence (when you have the same user in both).

DETAIL: the id the tables are independent of each other, so that the distinct work exactly as I said, removed from return.

---

From the @adventistaam comment, you can try using only the join, validating which date display:

SELECT DISTINCT
   IF(e.data_nascimento is null, i.nome, e.nome) as nome, 
   IF(e.data_nascimento is null, i.idade, e.idade) as idade, 
   IF(e.data_nascimento is null, i.data_nascimento, e.data_nascimento) as data_nascimento,
   IF(e.data_nascimento is null, i.cpf, e.cpf) as cpf
FROM internos i
JOIN externos e ON e.cpf = i.cpf
WHERE e.cpf = '12345678911'

Answer 2

Whereas the second select will not exist if it is complete in the first select, another way for you to do it would be to add in the Where select is not null.

SELECT id,
       nome,
       idade,
       data_nascimento,
       cpf
FROM internos
WHERE cpf = '12345678911'
  AND data_nascimento IS NOT NULL
UNION
SELECT id,
       nome,
       idade,
       data_nascimento,
       cpf
FROM externos
WHERE cpf = '12345678911'
  AND data_nascimento IS NOT NULL

Answer 3

Check if the date of birth is null and consider the appropriate table.

SELECT 
    CASE WHEN i.data_nascimento IS NULL THEN e.id ELSE i.id END AS id, 
    CASE WHEN i.data_nascimento IS NULL THEN e.nome ELSE i.nome END AS nome,
    CASE WHEN i.data_nascimento IS NULL THEN e.idade ELSE i.idade END AS idade,
    CASE WHEN i.data_nascimento IS NULL THEN e.cpf ELSE i.cpf END AS cpf
FROM internos i RIGHT JOIN externos e ON (i.cpf = e.cpf)
WHERE e.cpf = '12345678911';