How to Efficiently Join Tables with LIMIT 1 on a Joined Table?

Efficiently Joining Tables with LIMIT 1 on a Joined Table

Problem:

To join two tables while retrieving only one record from the joined table for each record in the primary table, specifically in contexts where multiple records exist in the joined table.

SOLUTION USING A SUBQUERY:

This approach effectively circumvents the error that arises when selecting multiple columns from the joined table while limiting results to a single record. The key is to utilize a subquery to retrieve the desired record's primary key. The subquery is then used to filter the joined table in the primary query.

SELECT
c.id,
c.title,
p.id AS product_id,
p.title AS product_title
FROM categories AS c
JOIN products AS p ON
p.id = (                                 --- the PRIMARY KEY
SELECT p1.id FROM products AS p1
WHERE c.id=p1.category_id
ORDER BY p1.id LIMIT 1
)

This query demonstrates superior performance, even when the joined table contains a significantly larger number of records than the primary table.

Alternative Approaches:

While the subquery approach is generally optimal, there are alternative methods available. However, it is important to consider their potential performance implications.

• Using GROUP BY and Array Aggregation (LIMIT NOT Applicable):

SELECT
  id,
  category_title,
  (array_agg(product_title))[1]  
FROM
    (SELECT c.id, c.title AS category_title, p.id AS product_id, p.title AS product_title
    FROM categories AS c
    JOIN products AS p ON c.id = p.category_id
    ORDER BY c.id ASC) AS a 
GROUP BY id, category_title;

• Using CROSS JOIN and ROW_NUMBER (LIMIT NOT Applicable):

SELECT
  c.id,
  c.title,
  p.id AS product_id,
  p.title AS product_title
FROM categories AS c
CROSS JOIN (SELECT product_id, title, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY id) AS rn
            FROM products) AS p
WHERE rn = 1;

The above is the detailed content of How to Efficiently Join Tables with LIMIT 1 on a Joined Table?. For more information, please follow other related articles on the PHP Chinese website!