SQL: Use table aliases across multiple select statements

How can I use an alias across different select statements or across a compound select statement? I want to do something like

SELECT * FROM bla AS bla2 INNER JOIN somethingelse
UNION
SELECT * FROM bla2 INNER JOIN anothersomething

But of course "bla2" is not recognized in the second select statement. (I realize doing this is not particularly useful here, but I'm trying to do something where "bla" is actually a whole sub select statement.)

Thanks.

(I'm using SQLite with Qt, if that makes any difference.

EDIT:开发者_运维问答 so I just learned that there is something called a CTE that might be useful here, but which SQLite does not support.)

You cannot reuse aliases this way, but you can alias a whole subquery, so you could write:

select
  *
from
  (select * from bla
  union all
  select * from blo) bli
  inner join anothersometing a on bli.id = a.id