Return orders containing at least one line for a requested product without returning an order more than once.

Order 10 contains the requested product on two separate lines. It still appears once. Order 11 has no matching line and does not appear.

• Do not use DISTINCT as a patch for an unnecessary row-multiplying join.
• Exclude cancelled orders.
• Ordering must be deterministic.

Because no line columns are needed in the result, I model the related table as an existence condition. The correlated subquery answers yes or no for each order and cannot multiply the order row. A composite index on OrderLines(ProductId, OrderId) supports starting from the selective product filter; the reverse order can be better when the query normally starts from a small page of orders.

SELECT o.OrderId, o.CustomerId, o.CreatedAt, o.Status
FROM dbo.Orders AS o
WHERE o.Status <> 'Cancelled'
  AND EXISTS
  (
      SELECT 1
      FROM dbo.OrderLines AS ol
      WHERE ol.OrderId = o.OrderId
        AND ol.ProductId = @ProductId
  )
ORDER BY o.CreatedAt DESC, o.OrderId DESC;

The best index order depends on whether product selectivity or the outer order page drives the plan. The important shape is a semi-join: one qualifying match is enough, and no deduplication step is required.

• The same product appears on multiple lines in one order.
• An order has many non-matching lines and one matching line.
• Two orders share the same CreatedAt.
• A cancelled order contains the product.

• Insert duplicate product lines for one order.
• Verify tie-breaking when timestamps match.
• Use a product that appears only in cancelled orders.

• Joining lines and returning duplicate order rows.
• Adding DISTINCT without understanding why duplicates appeared.
• Selecting line data when only existence is required.
• Ordering only by a non-unique timestamp.