title: How to reduce scope of subquery?
body: <p>I've got SQL running on MS SQL Server similar to the following:</p>
  
  <pre><code>SELECT
      CustNum,
      Name,
  FROM
      Cust
      LEFT JOIN (
  SELECT 
      CustNum, MAX(OrderDate) as LastOrderDate
          FROM
              Orders
          GROUP BY 
              CustNum) as Orders 
      ON Orders.CustNum = Cust.CustNum
  WHERE
      Region = 1
  </code></pre>
  
  <p>It contains a subquery to find the MAX record from a child table. The concern is that these tables have a very large number of rows. It seems like the subquery would operate on all the rows of the child table, even though only a very few of them are actually needed because of the WHERE clause on the outer query</p>
  
  <p>Is there a way to reduce the scope of the inner query? Something like adding a WHERE clause to only include the records that are included in the outer query? Something like</p>
  
  <pre><code>WHERE CustomerOrders.CustomerNumber = Customers.CustomerNumber -- Customers from the outer query.
  </code></pre>
  
  <p>I suspect that this is not necessary, but I am getting some push back from another developer and I wanted to be sure (my SQL is a little rusty).</p>