title: SQL Server - use Exists clause in Where and Select
body: <p>I have a view that's something like</p>
  
  <pre><code>CREATE VIEW OrdersView WITH SCHEMABINDING AS
  SELECT o.Id, o.OrderDate, o.LastName, o.TotalPrice, s.Status
  FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
  WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate &gt; s.StatusDate
  AND EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
  </code></pre>
  
  <p>The intent is to fetch all orders that have at least one line item of type 1 along with their current status.</p>
  
  <p>We're in the process of adding a second type of line item, and I have modified the view such that it will include orders that have at least one line item of type 1 or type 2:</p>
  
  <pre><code>CREATE VIEW OrdersView WITH SCHEMABINDING AS
  SELECT o.Id, o.OrderDate, o.LastName, o.TotalPrice, s.Status
  FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
  WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate &gt; s.StatusDate
  AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
    OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id))
  </code></pre>
  
  <p>Easy enough, but I've just had a requirement added to show whether an order contains line items of type 1 or type 2 (or both) in the grid where these results are displayed:</p>
  
  <pre>
  Order ID | T1 | T2 | Last name | Price    | Status
  ============================================================
  12345    | x  |    | Smith     | $100.00  | In Production
  12346    | x  | x  | Jones     | $147.23  | Part Dispatched
  12347    |    | x  | Atwood    | $12.50   | Dispatched
  </pre>
  
  <p>The only way I can think of is to do:</p>
  
  <pre><code>CREATE VIEW OrdersView WITH SCHEMABINDING AS
  SELECT o.Id, 
         CASE WHEN EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderID = o.Id) THEN 1 ELSE 0 END AS HasType1,
         CASE WHEN EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.ID) THEN 1 ELSE 0 END AS HasType2,
         o.OrderDate, o.LastName, o.TotalPrice, s.Status
  FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
  WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate &gt; s.StatusDate
  AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
    OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id))
  </code></pre>
  
  <p>But this smells wrong with the duplication of the <code>EXISTS</code> clauses.  Is there a better qway to write it?  Can I make it perform better?</p>