What’s the scope of table aliases?

This question is in the context of Sqlite but I wonder in general what’s the rule for aliases. Suppose I have the following query:

SELECT *
FROM (SELECT * FROM tableA as t1 inner join tableB as t2 on t1.id=t2.some_id) as t3,
     (SELECT * FROM tableC as t1 inner join tableD as t2 on t1.id=t2.some_id) as t4
WHERE t3.id=t4.id

The question is, will the two t1’s and t2’s clash? in my experience with multiple database vendors they won’t clash, but, could it confuse the optimizer to the point of producing a buggy result? I have this hypothesis for a query giving bogus results in Sqlite.

Answer

No, the aliases will not clash. They are known only in their respective subqueries.

Of course, the query will fail because of the trailing comma. And in general, you should avoid commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.