Anatomy of a SELECT statement – JOINS – Part 4

If your database is normalized, data points will only appear in the database one time.  For example, a customer name will only appear in one table,  customer address will appear in only one table and employee names will only appear in one table. This is a nice Wikipedia article on Normalization Well, what do you do if you have a request to create a report on sales that has a requirement of containing both the sales person name and the customer name.  Since the sales person name is in one table and the customer name is in another table, you will need to use the JOIN keyword. Given that, the purpose of a join is when there are columns in different tables that you want in the same result set.

There are a number of join types that can be used in SQL Server. In this post we will cover the logical join, what you using in your code.

Logical Join Types

INNER
OUTER
CROSS
FULL

Behind the scenes the query engine will use physical joins.  There are four of these, Nested Loop, Hash Match, Merge and starting in SQL Server 2017, Adaptive joins.  Although it is important to know these when working on query optimization, these will be covered in a future post.

Logical Join Requirements

Before getting into the types of joins, we should review the requirements of joins.

  • Column names do not need to match
  • Data types must be compatible
  • SELECT list does not need to include columns from every table
  • Although not an absolute requirement, it is highly recommended that you use table aliases when joining tables

Keep in mind, if the data types are not the same, the data will have to be converted.  This can be done explicitly using CAST or CONVERT, or it will be done with an implicit convert.

What columns To Join On

Typically the join will be on the Primary Key column in one table and a foreign key column in another.  However, there are times in which this will not be the case.

Table Alias

When using joins, it is important to give your tables an alias.  When use tables aliases, I like to have them be somewhat descriptive, like e for employee or s for sales. I also like to keep them the same length when ever possible and keep the to three or less characters.  These are just my preference and in no way is a requirement.

If you don’t use any aliases and the columns are not referenced properly in the SELECT statement and the column name you want to use exists on one or more tables in the FROM clause you will get an error.

In the statement below, you will see that the ProductID column does NOT use the alias as expected. Also notice the error below it, “Ambiguous column name ProductID”. This simply means that the column name exists in more than one table and SQL Server does not know which table to get the column from.

 

INNER Join

An INNER join would be used when you want to return data that has matching values in both tables in the join.

For example, you want to see all the products that have been sold. In order for a query to pull this data, there needs to be a matching value in both tables.  Using the AdventureWorks2014 database, the Product information is in the Production.Products table while the products sold is in the Sales.SalesOrderDetail table.  If you look at the image below, you will see that the two columns that we need to join on are both named ProductID.  However, as I stated before, that is not a requirement, but something you will see quite often.  The data types for both of these columns are also the same data type, INT.  However, that is also not a requirement, they just need to be similar data types.

 

To meet the requirements of the request of seeing all the products that have been sold, the ProductID must exist in both tables.  If product ID number 1 was sold at least one time, you will see it in the ProductID column in the SalesOrderDetail table.  The image below visualizes this.  The green circle is the Product table while the orange circle represents the SalesOrderDetail table.  The points where the two circles cross represents the values that can be found in both tables.

 

Using the code below, we can query the AdventureWorks2014 database to find all the products that have been sold at least one time.  What makes this work is that a ProductID will be returned ONLY if it appears in at least one order.

SELECT p.productid
, p.Name
, s.ProductID
, s.SalesOrderID
FROM production.product p
INNER JOIN [Sales].[SalesOrderDetail] s
ON p.ProductID = s.ProductID
ORDER BY p.ProductID

When you run the above query, will the get the results below.  Notice that the result set is ordered by ProductID in ascending order.  You will also notice that the ProductID 1 does not appear, despite existing in the Production.Product table.  This is because ProductID has not been sold.

This is confirmed by using the two queries below, one for the Product table and the other for the SalesOrderDetail table. Since this is an INNER JOIN, it is expected that productid number 1 would not be returned in the above result set.

Another small note about the INNER JOIN is that it is a the default join type.  In the statement below, you will notice that the INNER key word is no longer present, yet returns the exact same results set as above.

SELECT      p.productid
, p.Name
, s.ProductID
FROM production.product p
JOIN [Sales].[SalesOrderDetail] s
ON p.ProductID = s.ProductID
ORDER BY p.ProductID

OUTER JOIN

Lets change the requirements just a bit.  Instead of wanting to see all the products that have sold, we now want to know what products have NOT sold.  The OUTER join to the rescue!

An OUTER join will return all rows from the table on one side of the join and only matching values from the other table.

Starting with the LEFT OUTER JOIN, this join returns all rows from the table on the left and rows with a matching value on the left. We will use LEFT OUTER JOIN in this blog, however the OUTER key is technical optional, I just prefer to always use it.

SELECT p.productid
, p.Name
, s.ProductID
FROM production.product p
LEFT OUTER JOIN [Sales].[SalesOrderDetail] s
ON p.ProductID = s.ProductID

Below you will find the results of the above statement.  Notice that there are a number of rows that have NULL in the ProductID column from the SalesOrderDetail table.  This is what you will see when using an outer join and there is not a matching value in the table.  In this case, ProductID 530 does not have a matching value in the SalesOrderDetail table.

 

Of course if we switch this from a LEFT OUTER JOIN to a RIGHT OUTER JOIN SQL Server will now pull all the rows from the SalesOrderDetail table and the matching values from the Product table.

The code below is an example of a RIGHT OUTER JOIN.  All I did was change the word LEFT to RIGHT.  Now it will pull all the records from the SalesOrderDetail table and the matching values in the Product table.  One thing you will notice when you run this is that there not any NULL values in the p.productid column.  The reason for this is that referential integrity will not allow for a product that doesn’t exist in the Production.Product table to be entered into the table.

SELECT p.productid
, p.Name
, s.ProductID
FROM production.product p
RIGHT OUTER JOIN [Sales].[SalesOrderDetail] s
ON p.ProductID = s.ProductID

How are the LEFT and RIGHT tables determined

If you look at the two statements below, you will notice that the only difference is the last line.  In the first statement, the product table is listed first after the ON keyword, while in the second statement the SalesOrderDetail table is listed first.  Despite this change, both results had the exact same number of rows, 121,555.

SELECT p.productid
, p.Name
, s.ProductID
FROM production.product p
LEFT OUTER JOIN [Sales].[SalesOrderDetail] s
ON p.ProductID = s.ProductID

SELECT p.productid
, p.Name
, s.ProductID
FROM production.product p
LEFT OUTER JOIN [Sales].[SalesOrderDetail] s
ON s.ProductID = p.ProductID

However, if you switch the table order in the FROM clause, like in the statement below, you will see a big difference in the result set.

SELECT p.productid
, p.Name
, s.ProductID
FROM [Sales].[SalesOrderDetail] s
LEFT OUTER JOIN production.product p
ON p.ProductID = s.ProductID

This statement returns 121,317 rows.  This is every row from the SalesOrderDetail table and matching values in the Product table.  With some simple math, we can confirm this.  If you take the 121,555 and subtract the 212,317 you will get 238.  Using the code below, we will see that there are exactly 238 products that have not been sold. 

Now let’s get to the original question, what products have not been sold. In the example below we can simply add a WHERE clause and look for a any column from the right side table that is null.  The reason right side table, it because this is a left join.

SELECT p.productid
, p.Name
, s.ProductID
FROM production.product p
LEFT OUTER JOIN [Sales].[SalesOrderDetail] s
ON s.ProductID = p.ProductID
WHERE s.ProductID is null

When you run this you will see that there are 238 products that have not been sold.

Full Join

The code for this example can be found here: FullOuterJoin.

A full join is kind of a combination of the inner and both outer joins. Below you can see the two tables we will use for the example.

SELECT a.id
, a.name
, b.id
, b.name
FROM TableA a
FULL OUTER JOIN TableB b
ON A.id = b.id

If you run the above code you will see that it returns all rows that have matching values in each table, all the rows that are in TableA and not in TableB as well as the rows in Tableb that are not in TableA.

 

CROSS JOIN

A CROSS join is also known as a Cartesian product.  This type of join returns every possible combinations.

SELECT a.id
, b.id
FROM TableA a
CROSS JOIN TableB b

Below are the results.  The first ID column is from TableA while the second ID column is from TableB.  Notice that the results show each value from TableA and each value from TableB.  That is why you see 5 ones in the second column, there are 5 values in TableA.

Joining More than One Table

There are many times where you will need to join more than one table.  In this case we want to know the dates of each time a product was sold. In this case, we need to add the SalesOrderHeader table to our query. Although, there are three tables in the query, a join can only be between two table.

SELECT p.productid
, p.Name
, s.ProductID
, h.OrderDate
FROM [Sales].[SalesOrderDetail] s
INNER JOIN production.product p
ON p.ProductID = s.ProductID
INNER JOIN sales.SalesOrderHeader h
ON s.SalesOrderID = h.SalesOrderID

 

In a future post I will discuss the physical joins SQL Server will use to implement these logical joins.

Thank you for visiting my blog!!

 

 

 

 

 

Table Variable and Rollback

I am currently at PASS Summit in Seattle and learning a ton of new stuff.  There are a lot things that I have learned so far, but one thing really jumped out at me.  I attended Brent Ozar’s session on Cardinality Estimates and he showed that when you use table variables, the transaction does not get rolled back. That is what is great about SQL Server, there is always something to learn!!

If you run this code, you will see that when the select runs, it will actually not return any rows.

CREATE TABLE #test
(Name VARCHAR(100))

BEGIN TRAN
    INSERT #Test
(Name)
VALUES
(‘Dave’),
(‘Raf’)

ROLLBACK

SELECT * FROM #test

DROP TABLE #test

However, when you run this code, you will see that that rows are still in the variable even though the rollback command has completed.

DECLARE @TestV TABLE
(Name VARCHAR(100))

BEGIN TRAN
        INSERT @Testv
(Name)
VALUES
(‘Dave’),
(‘Raf’)

ROLLBACK

SELECT * FROM @TestV

This is another reason why table variables should be used with caution. Table variables do not get statistics on the columns which of course can be a performance killer!!!