SQL Server - using the Exists clause in the Where and Select section

Question

I have an idea that something like

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 > s.StatusDate AND EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id) 

The goal is to get all orders that have at least one type 1 line item together with their current status.

We add the second type of line item, and I changed the view so that it would include orders that have at least one line item of type 1 or 2:

 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 > s.StatusDate AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id) OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id)) 

Easy enough, but I just had a requirement added to show if the order contains rows of type 1 or 2 (or both) in the grid where these results are displayed:

  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

The only way I can think of is to do this:

 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 > s.StatusDate AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id) OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id)) 

But it smells bad of duplicating EXISTS sentences. Is there a better time to write? Can I make it work better?

Answer 1

you can HELP SAVE on OrderLineItemType1 and OrderLineItemType2, and then filter the rows where both of these columns are NULL in the WHERE clause.

Answer 2

One change that might be worth profiling (but not directly related to your specific question).

The following two lines:

 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 > s.StatusDate 

It’s better to write this as:

 FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId LEFT JOIN dbo.OrderStatus s_later on o.Id = s_later.OrderId and s_later.StatusDate > s.StatusDate WHERE s_later.OrderId is null 

I usually find that this works better (but this is one of those worth profiling in both directions).

LEFT JOIN tries to find later rows that apply to the same order, then the WHERE clause rejects any potential rows of the results in which such a match occurred, so the only matching row from s should be the last for this order.

Answer 3

Here you do not need EXISTS :

 SELECT o.Id, HasType1, HasType2, o.OrderDate, o.LastName, o.TotalPrice, s.Status FROM dbo.Orders o CROSS APPLY ( SELECT TOP 1 s.* FROM dbo.OrderStatus WHERE OrderId = o.Id ORDER BY StatusDate DESC ) s OUTER APPLY ( SELECT TOP 1 1 AS HasType1 FROM dbo.OrderLineItemType1 WHERE OrderID = o.Id ) olt1 OUTER APPLY ( SELECT TOP 1 1 AS HasType2 FROM dbo.OrderLineItemType2 WHERE OrderID = o.Id ) olt2