The problem at least with MSSQL is that these Table Valued Functions or User Defined Functions (scalar) are definitely not zero-cost abstractions. Another issu

The problem at least with MSSQL is that these Table Valued Functions or User Defined Functions (scalar) are definitely not zero-cost abstractions. Another issue is that the “SELECT” is also part of the function, which makes it even worse - so you can’t create just one function and reuse it all over the place. If only you could tell SQL that a certain JOIN always contains one row, or zero or one row (for a LEFT JOIN), then there would be more room for optimization.