Joins are bad aren't they ?

First up, I'm going to make a fairly large assumption in that you probably don't realise what you are asking of your data.

Imagine you have a book and I ask you for some information from it. If you don't know exactly what is being asked you are going to have to read the entire book from start to finish. Welcome to the term 'table scan'. Your book is your table and you read everything in order to get a small snippet of information.

Reading this book will take some time but a computerised version means you might not notice this duration as it can still be perceived as fast enough (a good data professional will look to negate this time spent).

If I tell you what chapter, page, line and word I'm after then you can flick through that book very quickly without having to bother reading the rest of the book. This is what a 'clustered index' is.

If your book was effectively an encyclopaedia then you don't want to pick up the entire series of books. This is what 'vertical partitioning' is (choosing the book we want and not picking up the other books) 

So, now lets go and get a second book.

Now I'm going to ask for something from Book A and also something from Book B and from the two I'll put this information together into something I want.

So, if I read Line 1 of Page 1 of Book A then read the entirety of Book B before reading Line 2 of Page 1 of Book A, then read the entirety of Book B again until we have reached the end of Book A then you will soon see that this is going to take a long time. 

Add in lots more books and it gets worse and worse. If I did a calculation every time I read a new line in Book A then this is what happens when you put calculations in your 'WHERE clause'. If I did this for Book B, this means that same calculation gets run a hell of a lot as we read this entire book multiple times and do this calculation each time we read a line in Book B. To understand this better you should have a look at 'Cartesian Products' as that is what you are asking for. If TableA has 10 rows and TableB also has 10 rows, with a Cartesian Product you will get back 10x10 (which is 100 as you should well know).

This is effectively why people state 'joins are bad'. In reality, it should be 'doing something stupid is bad'.

What I need to aim for is to find what I want from Book A very quickly and then what I want from Book B without reading both books multiple times. I can do this by getting my indexing right so that I cut out the data that I'm not interested in. I can simply find what I want from Book A by navigating the index to the Chapter, Page, Line and Word and then doing the same with Book B.

Now, stepping into indexes a little, if the index was the clustered index, this is the word that we want to get from each book. If it was a non-clustered index this is pretty much like first looking in the book's index and then finding the page and line to go and look up the word we want.

It really is that simple.

If you want to go a little further, you should consider the content from the following brilliant presentation http://www.youtube.com/watch?v=H7PJ1oeEyGg