In my previous post about logical joins I wrote about the most common type of SQL Server join, the INNER JOIN. Naturally the logical follow-up is to look at the OUTER JOIN. Syntax for OUTER JOIN is bit different from the INNER JOIN, as you need to define either LEFT, RIGHT or FULL keyword (more about FULL in another post). Unlike the INNER JOIN which only returns rows from the tables if there are equal values in join column, OUTER JOIN will return all the rows from either LEFT or RIGHT even if there is no matching value in the table you are joining.
While back I wrote this blog post about SQL Server joins, the focus back then being in the physical ones. So I figured that now would be a good time to re-visit the topic and look at the logical joins also. Where the physical joins are decided by the SQL Server engine the logical joins are the ones we write. There are quite a few different kind of logical joins, so I will be writing multiple posts about this topic.
I was recently involved in a query tuning work where we used synthetic, rather than production data, to validate the results of our query and index tuning work. We faced some issues with the generated data that had quite a severe impact on our testing, and that prompted me into writing this blog post. Lets start by first defining what is synthetic data. In my view synthetic data is data that resembles actual production data, but is artificial/generated. I have seen similar (and also more detailed) definitions elsewhere and I think it is a good one.
I also like to point out that there are plenty of good reasons for using synthetic data in testing, as production data is often strictly regulated and not easily available for testing purposes. However, you need to be certain that the synthetic data you are using is similar to what you have in production.
I was recently looking at some Execution Plans with a co-worker and we ended up discussing the different types of joins in a SQL Server and what implications they might have when it comes to query performance. While many of us are familiar with writing joins, as we usually don’t query just a single table, there are quite few things about the physical joins that may not quite obvious. In this post our focus will be in the physical joins, but we will also very briefly look at the different types of logical joins also.