I was a regular user of JOINs in SQL until I heard a podcast where the podcast people were discussing why SQL JOINs were not a good idea. I had never thought about this before. JOINs felt like the best way to combine data from multiple tables and get the result set desired.
JOINs is one of the foundations of RDBMS. If you use an RDBMS solution, you must be using JOINs. You store data using foreign keys in multiple tables and use JOINs to combine them to get the result desired.
Why were a lot of people writing about avoiding JOINS? I kept asking myself this question the first few weeks after I heard the suggestion.
After a year of launching the product, I realised why SQL joins should be avoided. Digging through the APM stats, almost all slow queries used had JOINs across two or more tables. Using JOINs over a large dataset turns out is expensive. Unless you are ready to throw a lot of hardware at the server hosting the database. As an upcoming startup, this was not an option.
I slowly started removing all JOINs from all the slow queries reported and porting them over to scripting solution used and used caching solutions to speed up transactions. Customers were happy, and so were the APM stats.
SQL joins on small datasets are great. One query to get data across multiple tables 👍️ . Just avoid using them over large datasets.
