Depends on the specific SQL flavor, e.g. https://www.postgresql.org/docs/9.1/queries-with.html

Logica instead seems to create tables and drop tables?

https://github.com/EvgSkv/logica/blob/main/examples/Logica_e...

It looks like Logica code isn't actually translated to a (large) SQL query, but Logica code is dynamically interpreted by some interpreter that calls into a SQL database as the virtual machine.

Not sure what Logica really provides here. Datalog usually comes with elaborate techniques to make sure only what's really needed is calculated instead of just generating all values every fact generation "iteration".