[MSM]

Depends what you mean by optional join- if you mean you want the flexibility to build up a list of columns you actually want data from on the fly, you would probably have good results from doing left joins to the tables that contain those columns while ensuring you have a unique column (PK, Unique constraint) you're joining to. Your query engine should be smart enough to realize that it can avoid joining that table.

The logic is that you've ensured the query engine that it'll never return more than one row from the optional table so if you're not returning any actual columns from that table there's no need to think about it. Without the unique constraints the query engine has no idea how many rows may be returned (even if you aren't selecting the data) so it still needs to go through the work.

[orf]

I mean a series of joins that are conditional on a user filter. For example, users with orders with invoices that are paid in the last 10 days.

Anyone can string-concat simple one table queries. That’s fine. The moment you add any non-trivial joins it gets exponentially more complex.