user1643156 user1643156 - 7 months ago 10
SQL Question

MySQL key name of data array fetched with UNION

The table

objects


id name is_animal
-----------------------
1 dog 1
2 cat 1
3 chair 0
4 rabbit 1


The query

(SELECT name AS animal_name FROM objects WHERE is_animal = 1)
UNION
(SELECT name AS object_name FROM objects WHERE is_animal = 0)


and the result

[animal_name] => dog
[animal_name] => cat
[animal_name] => rabbit
[animal_name] => chair // expected: [object_name] => chair


I've already used
AS
in the second SELECT, why does it return
animal_name
instead of
object_name
?

Answer

with UNION :

(SELECT name AS animal_name,'' object_name FROM objects WHERE is_animal = 1)
UNION
(SELECT '' as animal_name, name AS object_name FROM objects WHERE is_animal = 0)

with case :

select case is_animal when 1 name else '' end as  animal_name,
       case is_animal when 0 name else '' end as  object_name
from objects
Comments