Is there a more efficient approach other than using a co-related query for solving the below problem-CodePudding

I have three database tables: product, product_manufacturer, and product_manufacturer_warranties. The product table has a one-to-one mapping with product_manufacturer and the product_id is stored in the product_manufacturer table. The product_manufactuer table has a one-to-many mapping with the product_manufacturer_warranties table.

I need to write a query that retrieves all columns from the product table and two other columns that can be used to determine if a valid join exists for product and product_manufacturer and product_manufacturer and product_manufactuer_warranties respectively.

I have written the following co-related query that can handle the above scenario:

select product.*, pm.web_id,
        (   SELECT count(*)
            FROM product_manufacturer_warranty pmw
            WHERE pm.web_id = pmw.product_manufacturer_id)
            AS total_warranties
from product
left join product_manufacturer pm on product.web_id = pm.product_id

I wonder if there is a better or more efficient way of achieving this using SQL on the PostgreSQL server.

CodePudding user response：

Do the aggregation once, and join to the result:

select product.*, 
       pm.web_id,
       pmw.total_warranties
from product
  left join product_manufacturer pm on product.web_id = pm.product_id
  left join (   
     SELECT product_manufacturer_id, count(*) as total_warranties
     FROM product_manufacturer_warranty pmw
     group by product_manufacturer_id
  ) on pm.web_id = pmw.product_manufacturer_id)

CodePudding user response：

Yes, a more efficient way to write the same query would be to use a left join between the product_manufacturer and product_manufacturer_warranty tables, instead of a correlated subquery, like this:

SELECT product.*, pm.web_id, COUNT(pmw.product_manufacturer_id) AS 
total_warranties
FROM product
LEFT JOIN product_manufacturer pm ON product.web_id = pm.product_id
LEFT JOIN product_manufacturer_warranty pmw 
ON pm.web_id = pmw.product_manufacturer_id
GROUP BY product.web_id, pm.web_id

This way, the database only needs to perform one join instead of a separate subquery for each row in the product table. The GROUP BY clause ensures that the count of warranties for each manufacturer is aggregated correctly.