Joins and Transactions are a pretty big part of SQL. I'm no expert, but if my quick search results are right, both are lost in the separate file per table scenario.
Joins: https://simonwillison.net/2021/Feb/21/cross-database-queries...
Transactions: https://www.sqlite.org/atomiccommit.html#_multi_file_commit