MySQL entity and entity's aliases: How to remove UNION?

Question

I have these tables in database:

• entities(id, name, deleted)
• entities_aliases(entity_id, alias_id)
• aliases(id, name)

This query are works fine, but slow due UNION, notice how entities.name swapped with aliases.name and moved to last column:

SELECT entities.id, entities.name, entities.deleted, NULL AS main_name
FROM entities
UNION
SELECT entities.id, aliases.name, entities.deleted, entities.name AS main_name
FROM entities
JOIN entities_aliases ON entities_aliases.entity_id = entities.id
JOIN aliases ON entities_aliases.alias_id = aliases.id
ORDER BY name

This swapping is very important for me, I can't remove it, but UNION will get slower and slower as database develops in width (more fields and links) and depth (more records). How I can get rid of UNION?

Added: The output I need. For example, we have entity 1-AAA and his aliases 1-BBB and 2-CCC. The output should be:

• AAA
• BBB (AAA)
• CCC (AAA)

Answer 1

I think you can achieve it using LEFT JOINS and a few IFs:

SELECT e.id, IFNULL( a.name, e.name ) AS name, e.deleted, IF( a.name IS NULL, NULL, e.name ) AS main_name
FROM entities AS e
LEFT JOIN entities_aliases AS ea ON e.id = ea.entity_id
LEFT JOIN aliases AS a ON ea.alias_id = a.id;

If I understood what you wanted, you want "name" to be the alias name or the entity name if there's no alias (in this order), and main_name to be null when you don't have an alias, and the entity name when you do have an alias. I hope it helped!