Changing SQL NOT IN to JOINS
Hi guys,
Our goal is to get a script that will insert the missing product pairs - TaxCategory into an intermediate table (ProductTaxCategory)
The following script is working correctly, but we are trying to find a way to optimize it:
INSERT ProductTaxCategory
(ProductTaxCategory_TaxCategoryId,ProductTaxCategory_ProductId)
SELECT
TaxCategoryId
,ProductId
FROM Product pr
CROSS JOIN TaxCategory tx
WHERE pr.ProductId NOT IN
(
SELECT ProductTaxCategory_ProductId
FROM ProductTaxCategory
)
OR
pr.ProductId IN
(
SELECT ProductTaxCategory_ProductId
FROM ProductTaxCategory
)
AND
tx.TaxCategoryId NOT IN
(
SELECT ProductTaxCategory_TaxCategoryId
FROM ProductTaxCategory
WHERE ProductTaxCategory_ProductId = pr.ProductId
)
How can we optimize this query?
source to share
Try something like (full statement now):
INSERT INTO ProductTaxCategory
(ProductTaxCategory_TaxCategoryId,ProductTaxCategory_ProductId)
SELECT TaxCategoryId, ProductId
FROM Product pr CROSS JOIN TaxCategory tx
WHERE NOT EXISTS
(SELECT 1 FROM ProductTaxCategory
WHERE ProductTaxCategory_ProductId = pr.ProductId
AND ProductTaxCategory_TaxCategoryId = tx.TaxCategoryId)
EXISTS
c is (SELECT 1 ... WHERE ID=...)
often the best alternative to designs IN (SELECT ID FROM ... )
.
source to share
You can do LEFT JOIN
with ProductTaxCategory
and check NULLs
.
Something like that.
INSERT ProductTaxCategory
(
ProductTaxCategory_TaxCategoryId,
ProductTaxCategory_ProductId
)
SELECT p.TaxCategoryId, p.ProductId
FROM
(
SELECT TaxCategoryId, ProductId
FROM Product pr
CROSS JOIN TaxCategory tx
) p
LEFT JOIN ProductTaxCategory ptx
ON P.TaxCategoryId = ptx.ProductTaxCategory_TaxCategoryId
AND P.ProductId = ptx.ProductTaxCategory_ProductId
WHERE ptx.ProductTaxCategory_ProductId IS NULL
source to share
Use CROSS JOIN
andEXCEPT
INSERT ProductTaxCategory(ProductTaxCategory_ProductId, ProductTaxCategory_TaxCategoryId)
SELECT p.ProductID, tc.TaxCategoryId FROM Product p CROSS JOIN TaxCategory tc
EXCEPT
SELECT ProductTaxCategory_ProductId, ProductTaxCategory_TaxCategoryId FROM ProductTaxCategory
CROSS JOIN
will search for all possible pairs. EXCEPT
will bring you what is missing. Finally, you can put INSERT
them on a spreadsheet.
source to share