title: How to create high performance SQL select query, if I need a condition for referrer table's records?
body: <p>For example, I have 2 tables, which I need for my query, <code>Property</code> and <code>Move</code> for history of moving properties.</p>
  
  <p><a href="https://i.stack.imgur.com/R5vlq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R5vlq.png" alt="Property"></a></p>
  
  <p><a href="https://i.stack.imgur.com/eJJWA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eJJWA.png" alt="Move"></a></p>
  
  <p>I must create a query which will return all properties + 1 additional boolean column, <code>IsInService</code>, which will have value <code>true</code>, in cases, when <code>Move</code> table has a record for property with <code>DateTo = null</code> and <code>MoveTypeID = 1</code> ("In service"). </p>
  
  <p>I have created this query:</p>
  
  <pre><code>SELECT  
      [ID], [Name], 
      (SELECT COUNT(*) 
       FROM [Move]
       WHERE PropertyID = p.ID 
         AND DateTo IS NULL 
         AND MoveTypeID = 1) AS IsInService
  FROM 
      [Property] as p
  ORDER BY 
      [Name] ASC
  OFFSET 100500 ROWS FETCH NEXT 50 ROWS ONLY;
  </code></pre>
  
  <p>I'm not so strong in SQL, but as I know, subqueries are the evil :)</p>
  
  <p>How to create high performance SQL query in my case, if it is expected that these tables will include millions of records?</p>