Menu
I'm looking for a high-level, algorithmic understanding so that I can get a Big-O sense of what SQL-Server is doing to perform joins. Feel free to be concise, I'm not looking for the extremely nitty gritty. The thing that prompted me to understand how joins are implemented better is the scenario behind this question that I also just posted. I felt like they were ultimately two separate questions though, which is why I didn't combine them.
Thanks!
345
Different Types of SQL JOINs(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
Inner Join syntax basically compares rows of Table1 with Table2 to check if anything matches based on the condition provided in the ON clause. When the Join condition is met, it returns matched rows in both tables with the selected columns in the SELECT clause.
The main difference between the Left Join and Right Join lies in the inclusion of non-matched rows. Left outer join includes the unmatched rows from the table which is on the left of the join clause whereas a Right outer join includes the unmatched rows from the table which is on the right of the join clause.
The biggest difference between an INNER JOIN and an OUTER JOIN is that the inner join will keep only the information from both tables that's related to each other (in the resulting table). An Outer Join, on the other hand, will also keep information that is not related to the other table in the resulting table.
EDIT (2020-03-11): The links in this 9+ year old answer are all invalid today. I would delete the answer, but SO won't let me since it was accepted back when it was actually useful.
Original Answer:
Here's some reading to get you started.
79
Honestly if you are interested at that level of detail I would suggest you read Microsoft SQL Server 2008 Internals.
And learn to read execution plans. SQL Server has a pretty good optimization engine. It doesn't always do things the way we humans would expect though or even the same way for two queries that appear to us be similar.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
