L'ho risolta mettendo

select t.id deviceID, NULL label
from TABLE t
left join TABLE_SD sd on t.tablesd_id =sd.id
left join TABLE_C c on sd.tablec_id=c.id
where c.name LIKE ('%ubb%')
union
select t.id deviceID, d.label label
from TABLE trp
left join TABLE_D d on t.tabled_id =d.id
left join TABLE_C c on d.client_id=c.id
where c.name LIKE ('%ubb%')
order by label asc

Potete chiudere il thread