The Anatomy Of An INNER JOIN Query In SQL

Seven years ago, I've talked about how I much prefer INNER JOIN clauses over WHERE clauses whenever possible within a SQL query. But, I've never exactly codified how I think about the anatomy and the structure of an INNER JOIN. Now, that's not for lack of time - that's for lack of consciousness. Until recently, I don't think I had a true strategy for INNER JOINs; my approach worked, but it lacked discipline and understanding. In the last few weeks, however, I've started to really develop a solid plan for INNER JOIN construction.

After reading that introductory paragraph, your gut reaction may very well be something like, "Who cares - my RDMS (Relational Database Management System) performs that kind of optimization for me when it compiles the query." This may be true; it may not be. But, ultimately it's irrelevant because it misses the point - when you don't think about how your data is being used, you'll become complacent in how your data is being structured.

And, when that happens, you build slow queries that don't have to be slow.

Now, when I think about INNER JOINs, I think in terms of data that I have, and data that I need. The data that I have goes on the right, the data that I need goes on the left. When I do this, I can clearly see the flow of information. And, I can more easily see where I need to apply my table indicies: on the data that I need so that the database can locate it quickly.

With this structure, I find it very easy to see the flow of information. But, you still need to understand the volume of information that is being consumed. When you JOIN tables together, you want to put the most limiting tables higher up in the JOIN structure. This way, you minimize the data that you "have" (see above), which in turn, minimizes the data that you "need" (see above).

That's my current understanding. It's taken me a while to get here; but, I am constantly trying to better my understanding of SQL. So, if you see something here that doesn't make sense or, you have a suggestion, I am always excited to improve!