Number of tables in join and query performance
Recently I came across a standards in my project which strangely asserts that we should refrain from joining more five tables in SQL query. More than five tables in a query leads to performance degradation. Even though I strongly disagree with such assertion (regardless it's SQL Server or Oracle) I was wondering is there any suggestion as to how many tables we should use in join in SQL Server or Oracle. 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.? My understanding is since SQL is not a procedural language we should try to add more tables in JOIN provided that we are using the right columns for joining.