Of course, if you have loop detection in your business logic when writing to the database, then you can safely use UNION ALL always.

EDIT: Removed a bunch of stuff that was partly based on a faulty memory of how UNION works vs UNION ALL. Indeed, UNION ALL means the RDBMS does not need to keep around the whole result set.

Just to be clear, the reason I mentioned this in response to your original post is that UNION ALL doesn't have anything to do with the query recursing infinitely: it just allows the database engine to not keep previous results around. If you have a query that recurses infinitely, with UNION it will definitely exhaust the memory of the database engine, and with UNION ALL it will never stop returning results, which may exhaust the memory of the database client (unless it discards the results).