{"id":501,"date":"2010-05-16T21:17:00","date_gmt":"2010-05-16T21:17:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Determining-the-position-of-search-arguments-in-a-join.aspx"},"modified":"2016-06-17T04:56:58","modified_gmt":"2016-06-17T11:56:58","slug":"determining-the-position-of-search-arguments-in-a-join","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/determining-the-position-of-search-arguments-in-a-join\/","title":{"rendered":"Determining the position of search arguments in a join"},"content":{"rendered":"

As a follow-on discussion to my\u00a0SQL Server Magazine Q&A titled \u201cDetermining the position of search arguments in a join<\/a>\u201d where I was responding to a reader question about whether or not search arguments could be moved up into the FROM clause instead of placed in the WHERE clause and whether or not it would improve performance. While I was so focused on creating a scenario to highlight the problems when doing this, I missed a great point about what happened in my specific<\/em> example. Fellow SQL Server MVP, Phil Brammer (blog<\/a> | twitter<\/a>)\u00a0brought up this point first and it sparked a very passionate discussion on the SQL Server MVP forum. As a result, I thought I\u2019d take a few minutes to create a few more examples and state a few key points. The most important of which is that search arguments should not be moved from the WHERE clause into the FROM clause unless you really know what you\u2019re doing – you may completely change the meaning of the query\u2026 Let me explain. <\/span><\/span><\/p>\n

Details:<\/span><\/strong> <\/span><\/p>\n

In the prior Q&A, Listing 1 and Listing 2 show INNER JOINs that describe the Customers along with some of their Sales information (specifically: OrderDate, AccountNumber, FirstName, LastName, and StateProvinceCode). In the joins, we asked for only Customers with a RegionCode of US. Regardless of having the search argument in the WHERE clause or the FROM clause, SQL Server executes the same plan, has the same performance and returns the same data. While people often state that a WHERE clause applies to the entire set (and also imply order), that\u2019s NOT the case in terms of how the join is processed. SQL Server knows that the search argument applies to the entire set but it can certainly apply the filter prior to the join if it\u2019s selective enough and would help improve performance. Moving the search argument up into the FROM clause is not a \u201ctip\u201d for improving performance. However, if all of the joins are inner joins, then it really doesn\u2019t matter. You can certainly move the search arguments up; they won\u2019t change the result set and it\u2019s very unlikely that they\u2019ll change the query plan. Listing 1 and Listing 2 were both executed at the same time to produce these two plans: <\/span><\/p>\n

\n

\n

\"\" <\/span><\/p>\n

First, notice that they are 50% each. Also, without worrying about the plan itself, notice that each plan has the same pattern, the same percentages, and the same join types. These are in fact, the same plan \u2013 Listing 1 has the search argument in the WHERE clause and Listing 2 has the search argument in the FROM clause. <\/span><\/p>\n

To illustrate how moving a search argument could affect results, I created OUTER joins, added a somewhat random search argument and then did the same thing \u2013 Listing 3 was written as an outer join and Listing 4 was a copy of that query with the search argument moved up into the FROM clause. While my examples (solely looking at the results between Listing 3 and Listing 4<\/em><\/strong>) prove the point that a search argument cannot be moved from the WHERE clause into the FROM clause without understanding the effects, my example also introduced another phenomena. It was this phenomena that sparked debate. When a search argument exists on an inner table of an outer join, the purpose of the outer join is effectively lost. In fact, the query is not likely to even work the way you expect. To illustrate this, I\u2019ve created a [quick] Venn diagram of what\u2019s logically happening: <\/span><\/p>\n

\n

\"\"<\/div>\n

\n

\n

In fact, the two following queries will produce identical results: <\/span><\/p>\n

— The original Listing 3 query
\n<\/span>SELECT<\/span> so.<\/span>OrderDate,<\/span> c.<\/span>AccountNumber,<\/span> p.<\/span>FirstName
\n<\/span>\u00a0\u00a0\u00a0\u00a0 ,<\/span> p.<\/span>LastName,<\/span> sp.<\/span>StateProvinceCode
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>Person AS<\/span> p
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> c.<\/span>PersonID =<\/span> p.<\/span>BusinessEntityID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>Address<\/span> AS<\/span> pa
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>ShipToAddressID =<\/span> pa.<\/span>AddressID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>StateProvince AS<\/span> sp
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> sp.<\/span>StateProvinceID =<\/span> pa.<\/span>StateProvinceID
\n<\/span>WHERE<\/span> sp.<\/span>CountryRegionCode =<\/span> ‘US’
\n<\/span><\/span>ORDER<\/span> BY<\/span> sp.<\/span>StateProvinceCode,<\/span> so.<\/span>OrderDate<\/span>\u00a0<\/span><\/p>\n

— Listing 3 is really an inner join
\n<\/span>SELECT<\/span> so.<\/span>OrderDate,<\/span> c.<\/span>AccountNumber,<\/span> p.<\/span>FirstName
\n<\/span>\u00a0\u00a0\u00a0\u00a0 ,<\/span> p.<\/span>LastName,<\/span> sp.<\/span>StateProvinceCode
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Person.<\/span>Person AS<\/span> p
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> c.<\/span>PersonID =<\/span> p.<\/span>BusinessEntityID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Person.<\/span>Address<\/span> AS<\/span> pa
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>ShipToAddressID =<\/span> pa.<\/span>AddressID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Person.<\/span>StateProvince AS<\/span> sp
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> sp.<\/span>StateProvinceID =<\/span> pa.<\/span>StateProvinceID
\n<\/span>WHERE<\/span> sp.<\/span>CountryRegionCode =<\/span> ‘US’
\n<\/span><\/span>ORDER<\/span> BY<\/span> sp.<\/span>StateProvinceCode,<\/span> so.<\/span>OrderDate<\/span>\u00a0 <\/span><\/p><\/blockquote>\n

A better example \u2013 which preserved the purpose of the outer join \u2013 would be to place a search argument on the table which is our outer table in the query. Before I show the query itself, let\u2019s get some background on the data here. <\/span><\/p>\n

In the Sales.Customer table there are 3,520 Customers in TerritoryID 1 (the northwestern US): <\/span><\/p>\n

SELECT<\/span> COUNT<\/span>(*)<\/span>
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>WHERE<\/span> c.<\/span>TerritoryID =<\/span> 1 — 3520 customers<\/span><\/span>\u00a0<\/span><\/p><\/blockquote>\n

These Customers represent a total number of Sales of 4,594 orders: <\/span><\/p>\n

SELECT<\/span> COUNT<\/span>(*)<\/span>
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>WHERE<\/span> c.<\/span>TerritoryID =<\/span> 1\u00a0 — 4594 orders<\/span><\/span>\u00a0<\/span><\/p><\/blockquote>\n

There are 92 customers that have never placed an order: <\/span><\/p>\n

SELECT<\/span> COUNT<\/span>(*)<\/span>
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>WHERE<\/span> NOT<\/span> EXISTS<\/span> (<\/span>SELECT<\/span> *
\n<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID)
\n<\/span><\/span>AND<\/span> c.<\/span>TerritoryID =<\/span> 1 — 92 customers have not placed orders<\/span><\/span>\u00a0<\/span><\/p><\/blockquote>\n

If we want to see ALL Customers in TerritoryID 1 \u2013 regardless of whether or not they have placed an order and with their order information if they have, we can use an outer join to do this. For those customers who have not placed an order, NULLs will be produced: <\/span><\/p>\n

SELECT<\/span> so.<\/span>OrderDate,<\/span> c.<\/span>AccountNumber,<\/span> p.<\/span>FirstName
\n<\/span>\u00a0\u00a0\u00a0\u00a0 ,<\/span> p.<\/span>LastName,<\/span> sp.<\/span>StateProvinceCode
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>Person AS<\/span> p
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> c.<\/span>PersonID =<\/span> p.<\/span>BusinessEntityID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>Address<\/span> AS<\/span> pa
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>ShipToAddressID =<\/span> pa.<\/span>AddressID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>StateProvince AS<\/span> sp
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> sp.<\/span>StateProvinceID =<\/span> pa.<\/span>StateProvinceID
\n<\/span>WHERE<\/span> C.<\/span>TerritoryID =<\/span> 1 — 4686 rows
\n<\/span><\/span>ORDER<\/span> BY<\/span> sp.<\/span>StateProvinceCode,<\/span> so.<\/span>OrderDate<\/span>\u00a0<\/span><\/p><\/blockquote>\n

This is different from an inner join because the INNER join will return only those customers that have a matching row in each and every joined table. In this case, all 3,520 customers have a matching row in Person.Person, Person.Address and Person.StateProvince. As a result, the inner join produces all 3,520 customers with their sales for 4,594 total rows. If you notice, the outer join above returned 4686 rows (4686 – 4594 = 92). <\/span><\/p>\n

SELECT<\/span> so.<\/span>OrderDate,<\/span> c.<\/span>AccountNumber,<\/span> p.<\/span>FirstName
\n<\/span>\u00a0\u00a0\u00a0\u00a0 ,<\/span> p.<\/span>LastName,<\/span> sp.<\/span>StateProvinceCode
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Person.<\/span>Person AS<\/span> p
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> c.<\/span>PersonID =<\/span> p.<\/span>BusinessEntityID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Person.<\/span>Address<\/span> AS<\/span> pa
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>ShipToAddressID =<\/span> pa.<\/span>AddressID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Person.<\/span>StateProvince AS<\/span> sp
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> sp.<\/span>StateProvinceID =<\/span> pa.<\/span>StateProvinceID
\n<\/span>WHERE<\/span> C.<\/span>TerritoryID =<\/span> 1 — 4594
\n<\/span><\/span>ORDER<\/span> BY<\/span> sp.<\/span>StateProvinceCode,<\/span> so.<\/span>OrderDate<\/span>\u00a0<\/span><\/p><\/blockquote>\n

Using this query as our example, let\u2019s go back to the original discussion about whether or not moving the search argument can improve performance\u2026 In the query below, I *solely* move the search argument (c.TerritoryID = 1) up into the FROM clause for the following query: <\/span><\/p>\n

SELECT<\/span> so.<\/span>OrderDate,<\/span> c.<\/span>AccountNumber,<\/span> p.<\/span>FirstName
\n<\/span>\u00a0\u00a0\u00a0\u00a0 ,<\/span> p.<\/span>LastName,<\/span> sp.<\/span>StateProvinceCode
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND<\/span> C.<\/span>TerritoryID =<\/span> 1 — 20986 rows
\n<\/span><\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>Person AS<\/span> p
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> c.<\/span>PersonID =<\/span> p.<\/span>BusinessEntityID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>Address<\/span> AS<\/span> pa
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>ShipToAddressID =<\/span> pa.<\/span>AddressID
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Person.<\/span>StateProvince AS<\/span> sp
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> sp.<\/span>StateProvinceID =<\/span> pa.<\/span>StateProvinceID
\n<\/span>ORDER<\/span> BY<\/span> sp.<\/span>StateProvinceCode,<\/span> so.<\/span>OrderDate<\/span>\u00a0<\/span><\/p><\/blockquote>\n

And it returns a drastically different result set of 20,986 rows. What\u2019s happened here? <\/span><\/p>\n

In this case, we\u2019ve preserved ALL customer rows and then found the matching rows for ONLY those customers where TerritoryID = 1. To show you the numbers, it breaks down like this: <\/span><\/p>\n

There are 19,820 customers \u2013 of which, 3,520 are in TerritoryID 1. Of these 3,520 customers in TerritoryID 1, all but 92 have placed orders \u2013 for a total of 4,594 orders. Bringing everything together: <\/span><\/p>\n

19,820 customers \u2013 3,520 in TerritoryID 1 = 16,300 rows <\/span><\/p>\n

.4,594 orders + 92 customers that haven\u2019t placed an order (remember \u2013 all customers are preserved in the outer join) = 4,686 rows <\/span><\/p>\n

16,300 + 4,686 = 20,986 rows <\/span><\/p>\n

Bringing this all together and to quote fellow MVP Hugo Kornelis (blog<\/a>) in the discussion: “For OUTER joins, moving predicates does change the results. This makes performance comparisons moot. If a query gives incorrect results,\u00a0I don’t care if it’s faster or slower than one that does yield the desired results.”<\/em> <\/span><\/p>\n

This is absolutely the most important point. You cannot just move a search argument \u201cup\u201d into the FROM without repercussions. In general, I recommend search arguments to be in the WHERE clause and join conditions in the FROM clause. <\/span><\/p>\n

But, to make it even better, I thought I\u2019d end with an even more simplified example of the sets described above: <\/span><\/p>\n

SELECT<\/span> c.<\/span>CustomerID,<\/span> so.<\/span>SalesOrderNumber,<\/span> so.<\/span>OrderDate
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>WHERE<\/span> c.<\/span>TerritoryID =<\/span> 1 — Northwest US, 4594 rows
\n<\/span><\/span>go<\/span>\u00a0<\/span><\/p>\n

— in this simple inner join
\n<\/span>SELECT<\/span> c.<\/span>CustomerID,<\/span> so.<\/span>SalesOrderNumber,<\/span> so.<\/span>OrderDate
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 INNER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND<\/span> c.<\/span>TerritoryID =<\/span> 1 — Northwest US, 4594 rows
\n<\/span><\/span>go<\/span><\/p>\n

— What if we want ALL Customers in the Northwest of the US,
\n<\/span>— regardless of whether or not they placed an order:
\n<\/span>SELECT<\/span> c.<\/span>CustomerID,<\/span> so.<\/span>SalesOrderNumber,<\/span> so.<\/span>OrderDate
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>WHERE<\/span> c.<\/span>TerritoryID =<\/span> 1 — Northwest US, 4594 rows
\n<\/span><\/span>go<\/span> — Northwest US, 4686 rows<\/span><\/span>\u00a0<\/span><\/p>\n

— So, now what if we thought that the performance could be
\n<\/span>— better if we pushed this up into the FROM clause…
\n<\/span>SELECT<\/span> c.<\/span>CustomerID,<\/span> so.<\/span>SalesOrderNumber,<\/span> so.<\/span>OrderDate
\n<\/span>FROM<\/span> Sales.<\/span>Customer AS<\/span> c
\n<\/span>\u00a0\u00a0\u00a0\u00a0 LEFT<\/span> OUTER<\/span> JOIN<\/span> Sales.<\/span>SalesOrderHeader AS<\/span> so
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON<\/span> so.<\/span>CustomerID =<\/span> c.<\/span>CustomerID
\n<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND<\/span> c.<\/span>TerritoryID =<\/span> 1 — 20986 rows<\/span><\/span><\/p>\n

— Absolutely ALL customers, regardless of whether or not they
\n<\/span>— placed an order. For the Northwest territory we will see their
\n<\/span>— order information but for all other customers (even those that
\n<\/span>— have placed an order), we will NOT see their order information.
\n<\/span>go<\/span>\u00a0 <\/span><\/p><\/blockquote>\n

What’s probably most important is for you to thoroughly review the output of the last result set (of 20,986 rows). While I’ve explained the number – do you understand the results? Out of the 3,520 customers in TerritoryID 1 – 3,428 of them create 4,594 sales. For ONLY\u00a0these sales, we see the SalesOrderNumber and SalesOrderDate. For all other customers – even those that have placed orders – we see NULLs. Why? Because moving this search argument up into the FROM has defined the criteria for the INNER join *BEFORE* the outer join is performed. This is why we see NULLs for those that have placed orders. If you order the results by TerritoryID this may help you better visualize what’s happening!<\/span><\/p>\n

Thanks for reading and thanks for the comments and\u00a0discussions… I love the SQL Community for its never ending passion!<\/span><\/p>\n

Cheers,
\nKimberly<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

As a follow-on discussion to my\u00a0SQL Server Magazine Q&A titled \u201cDetermining the position of search arguments in a join\u201d where I was responding to a reader question about whether or not search arguments could be moved up into the FROM clause instead of placed in the WHERE clause and whether or not it would improve […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40,49,69],"tags":[],"class_list":["post-501","post","type-post","status-publish","format-standard","hentry","category-joins","category-opinions","category-sql-server-magazine"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/501","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=501"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/501\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}