Solution for Alternate way of comparing two columns in a table performance wise in SQL Server
is Given Below:
I have below query and getting correct result. But I want to know which query is good performance/code standard wise from the below queries.
DECLARE @TBL AS TABLE (Column1 INT NULL ,Column2 INT NULL) INSERT INTO @TBL(Column1,Column2) VALUES(25,25) ,(21,NULL)
SELECT * FROM @TBL WHERE COALESCE(Column1,'') <> COALESCE(Column2,'') OR(Column1 IS NULL AND Column2 IS NOT NULL)
SELECT * FROM @TBL WHERE ISNULL(NULLIF(Column1, Column2), NULLIF(Column2, Column1)) IS NOT NULL OR(Column1 IS NULL AND Column2 IS NOT NULL)
SELECT * FROM @TBL WHERE ISNULL(Column1,'') ! = ISNULL(Column2,'') OR(Column1 IS NULL AND Column2 IS NOT NULL)
The result should be
Column1 Colum2 21 Null
Can anyone please suggest for the above.
I am not fully sure what you like to achieve and therefore assumed the following question for my answer:
Return every row where Column1 is not equal to Column2 independently whether they are null or not.
This question I would solve like this, avoiding any functions which I assume must be slower.
SELECT * FROM @TBL WHERE (Column1 <> Column2) OR ((Column1 IS NULL) AND (Column2 IS NOT NULL)) OR ((Column2 IS NULL) AND (Column1 IS NOT NULL));
The final answer you find out through measuring in your environment.