An crucial point for me was realizing that "recursive CTEs" are not really recursive but better understood as iterative, in other words a loop. The results of each iteration are fed into the next iteration, until no new result is produced.
Regular expressions are a particularly interesting example because you brought up 'loops', so I'm assuming you are interested in how you can implement some of these recursive definitions on a computer.
So for regular expressions a common technique is to compile them to a finite state machine. You can model that in your computer as one block of eg assembly instruction per machine state. To move to a different state, you just 'jmp' to the corresponding code's address.
That's pretty fun to work out, but I still don't see anything I would describe as a 'loop' here; but the recursive analysis still makes perfect sense.
Yes, some programming languages have special purpose looping constructs. But they are of limited usefulness, and not particularly fundamental: they usually get compiled away.
When you add an explicit stack to help you keep state then you can have recursion be the same as iteration. Normally the stack you get in recursion is implicit rather than explicit.
It's important to differentiate between tail-recursive functions and non-tail-recursive functions. Compilers will often convert tail-recursive functions into their iterative counterparts. See: https://en.wikipedia.org/wiki/Tail_call
In contrast, non-tail-recursive functions will make the call stack grow with each call.
