SQL Server remove EXCEPT and just have in WHERE/AND statements-CodePudding

Hi I have the following SQL that I want to refactor and remove the EXCEPT

The SQL chunk is as follows:

UPDATE pv SET pv.EndDate = @newEndDate, pv.AuditUser = @auditUser
FROM PriceValues pv
INNER JOIN Prices p ON pv.PriceId = p.Id
INNER JOIN (
            SELECT Id FROM #RowsIWant 
            EXCEPT
            SELECT p.Id
            FROM PriceValues pv
            INNER JOIN #RowsIWant AS p ON pv.PriceId = p.Id
            WHERE pv.StartDate > @currentExpiryDate
            AND @newEndDate >= pv.StartDate
) up ON p.Id = up.Id
WHERE pv.EndDate = @currentExpiryDate
AND p.Active = 1

How can I remove the EXCEPT and just add extra AND's to the bottom

Originally I though I could just reverse the greater than symbols like this example, but I'm not convinced this is right and my mind cant seem to figure it out!

UPDATE pv SET pv.EndDate = @newEndDate, pv.AuditUser = @auditUser
FROM #RowsIWant AS P
INNER JOIN PriceValues AS PV ON pv.PriceId = P.PriceId
WHERE pv.EndDate = @currentExpiryDate
AND pv.StartDate < @currentExpiryDate
AND @newEndDate <= pv.StartDate
AND p.Active = 1

CodePudding user response：

This query:

SELECT Id FROM #RowsIWant 
EXCEPT
SELECT p.Id
FROM PriceValues pv
INNER JOIN #RowsIWant AS p ON pv.PriceId = p.Id
WHERE pv.StartDate > @currentExpiryDate
AND @newEndDate >= pv.StartDate

Is logically equivalent to:

SELECT p.Id
FROM #RowsIWant  pv
LEFT JOIN PriceValues AS p ON pv.PriceId = p.Id
WHERE pv.Id IS NULL 
    AND pv.StartDate > @currentExpiryDate
    AND @newEndDate >= pv.StartDate

The basic concept is that you can use left join, to find where there isn't a match.

CodePudding user response：

You can change the first part to a normal join, and the second half to NOT EXISTS

UPDATE pv
SET pv.EndDate = @newEndDate,
    pv.AuditUser = @auditUser
FROM PriceValues pv
INNER JOIN Prices p ON pv.PriceId = p.Id
INNER JOIN #RowsIWant riw ON p.Id = riw.Id
WHERE pv.EndDate = @currentExpiryDate
  AND p.Active = 1
  AND NOT EXISTS (SELECT 1
    FROM PriceValues pv2
    WHERE pv2.PriceId = p.Id
      AND pv2.StartDate > @currentExpiryDate
      AND @newEndDate >= pv2.StartDate
  )

You could also use LEFT JOIN / IS NULL like the other answer, but that is generally not as efficient.

Don't fall into the trap of using NOT IN, because it doesn't deal well with nulls.