More than five tables in a query leads to performance degradation.
A join will increases the cost of running a query to some degree no matter what. But what's the right thing to do ultimately depends on what's ideal or acceptable for any given project.
For example. I have a 1-column table that contains 1 million row, whose only column is an ordered bigint clustered index. Make 10 queries ranging from no join to 10 joins, and compare execution plan and execution time. You'll see the increased cost and time pretty much in a linear manner. In my personal environment, SELECT * with no join might take 5 seconds to complete while 6 joins might take 6 seconds. What if the table had only 10,000 records? It will run significantly faster even if the number of joins is doubled (in my environment <200ms).
Now, if the only way to get the right data set is by joining 10 tables and it takes 5 seconds, is that good or bad? If the speed is critical to your application, like high-speed stock trading, then you might want to reconsider. What if it was a report for sales department?
Does it depends on the database type (SQL Server Vs Oracle Vs MySQL)
or number of rows in the tables, indexing, INNER Vs OUTER Join,
cardinality of the tables joined, etc.?
Database product, yes and no. Each products have different optimization logic. So one might not execute a query the same as others. One might be faster than others in some cases and vice versa. Everything else you mentioned affects performance, including selectivity. OUTER can be slower than INNER. There are occasions where OUTER joins can be faster or costs less.
Of course, my very simple example is far from any real-life scenario but the underlying logic is the same. How many you should join depends on your environment and your requirements. Things you mentioned will affect the performance, and if the query is running below acceptable level or started having a performance issue, you should examine them. But none of them are relevant to how many joins you SHOULD have.
