How to Limit SQL Joins to Only the First Row of a Related Table?

Restricting SQL Joins to the First Related Row

Efficiently joining multiple tables in SQL is crucial, but sometimes we only need the first row from a related table. This article demonstrates how to limit a join to a single row, avoiding duplicate rows when dealing with one-to-many relationships.

Let's examine a common scenario:

We have two tables, Orders and LineItems. While most orders have one line item, some may have multiple. A standard INNER JOIN would lead to duplicated order rows if multiple line items exist.

To prevent this, we'll limit the join to only the first LineItems row per order. Here are two effective methods:

Using CROSS APPLY (SQL Server 2005 and later):

This approach offers a cleaner, more readable solution:

<code class="language-sql">SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2</code>

Employing an Inner Join with a Subquery (SQL Server pre-2005):

For older SQL Server versions, this method achieves the same result:

<code class="language-sql">SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )</code>

Important Consideration: TOP 1 without ORDER BY yields non-deterministic results. To consistently select a specific row, always include an ORDER BY clause in the inner query to specify the desired sorting criteria.

The above is the detailed content of How to Limit SQL Joins to Only the First Row of a Related Table?. For more information, please follow other related articles on the PHP Chinese website!