SQLStarters

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.

Our Environment

For this post I will be using the following schema with some automatically generated data. As you can see there are 4 tables, Example.Customers being the one the other tables are linked to.

These tables are part of the Example database we provide to our developers, we have designed and created following our defined best practices. But enough about that, let’s get started with the joins!

Inner Join

Inner Joins are by far the most common type of join you are likely to write. By quick estimate, when I write or work with joins, they are Inner Joins 99% of the time. Inner Joins are also sometimes called equi-joins as they only return rows from both tables that have equal values in the join predicate columns. The logic behind the Inner Join is following:

• Take rows from Table A
• Match rows from Table A with rows in Table B by using join predicate
• Print joined rows from Table A and Table B

The very professional looking drawing below (did it all by myself!) is an attempt to display the Inner Join graphically.

There are two different syntax for writing the Inner Joins, old one and the new one. In my query examples, I will be using only the new one (and so should you) because, at least from my point of view it’s easier to see what you are actually joining. Also the INNER keyword is optional, but at least I feel that using it makes the code easier to read.

Example 1: Simple Inner Join

Our first example is the most basic type of join, we print out rows of customers and the cities they live in. There is absolutely no need to include the RID columns that we use in the example queries as join predicates, but I have done that so you can better see how tables are linked together.

SELECT C.CustomerName
   ,C.PostalCityRID
   ,C2.RID
   ,C2.CityName FROM Example.Customers AS C
INNER JOIN Example.Cities AS C2 ON C.PostalCityRID = C2.RID

Here’s the first 10 rows produced by the query

Example 2: Inner Join with multiple tables and filtering

The example above is the simplest Inner Join query you can write, but very often you have more than two tables to get data from, and equally often you need to perform some filtering on the results, rather than fetching everything.  In the next example we want to find all the customers, who live in a specific city and who receive their orders by specific shipping method.

Again the RID columns are left only for the purpose of displaying the join predicate.

SELECT C.CustomerName
   ,C.DeliveryMethodRID
   ,DM.RID AS [DeliveryRID]
   ,DM.DeliveryMethodName
   ,C.PostalCityRID
   ,C2.RID AS [CityRID]
   ,C2.CityName
FROM Example.Customers AS C
   INNER JOIN Example.Cities AS C2 ON C.PostalCityRID = C2.RID
   INNER JOIN Example.DeliveryMethods AS DM ON C.DeliveryMethodRID = DM.RID
WHERE DM.DeliveryMethodName = N'Train';

Let’s look at the results then, for the first 10 rows.

And with that example, we wrap up this post about the Inner Joins. In my next post, we’ll look at the Outer Joins with similar examples.