Get record from SQL Server from two tables with optimized code

November 18, 2021

I have two tables – one is Product and the other is Productimage. In product image table, more than one image can exist of every product. Every image set ISDEAULT 1 or 0. Now I want every product from product table whose all image’s ISDEAULT is 0.

I wrote this query

SELECT DISTINCT(pd.ProductId) 
FROM Product pd
INNER JOIN ProductImage img ON img.ProductId =  pd.ProductId
WHERE NOT EXISTS (
    SELECT pdi.ProductId 
    FROM ProductImage pdi 
    WHERE pdi.IsDefault = 1 
    AND pdi.ProductId = pd.ProductId
)

It works correctly but my senior told me to use another approach.

Please give me optimized query

>Solution :

You can use conditional aggregation to check for IsDefault = 1

This means you are only querying the ProductImage table once

SELECT  pd.ProductId
FROM Product pd
JOIN ProductImage img ON img.ProductId =  pd.ProductId
GROUP BY
  pd.ProductId
HAVING COUNT(CASE WHEN img.IsDefault = 1 THEN 1 END) = 0;

If you don’t care if it has any ProductImage then you can just do

SELECT  pd.ProductId
FROM Product pd
WHERE NOT EXISTS (SELECT 1
    FROM ProductImage img
    WHERE img.ProductId =  pd.ProductId
    AND img.IsDefault = 1);