CODEWITHSUNDEEP
sqlsql-servert-sql
MrProgram
MrProgram

Reputation: 5242

Are these SQL queries equivalent?

I'm quite new to SQL. The first query is "correct" but I want to rewrite it so that it returns nothing instead of "Ok". Query #2 is my try. According to me, it is correct because the JOIN condition separates all rows where the two dates are different. But I'm not sure at this and as I said I'm kind a new to this.. The problem is that I have no test-data right to cross verify. Could you please help?

1

DECLARE @date1 datetime, 
                     @date2 datetime
SELECT  @date1 = Max(date) FROM table1
WHERE 1 = 1
        AND id = 1
        AND id2 = 11

SELECT  @date2 = Max(date) FROM table2
WHERE 1 = 1
        AND id = 2
        AND id2 = 11

SELECT 
        CASE 
                     WHEN COALESCE(@date1,0) = @date2 
                                 THEN 'Ok'
                     WHEN  CONVERT(TIME,GETDATE()) < '19:00:00' 
                                 THEN  'Ok'
                                 ELSE  'Not Ok'
        END AS Warning

2:

DECLARE  @date1 datetime
    ,@date2 datetime
    ,@id int = 1

SELECT  @date1 = COALESCE(MAX(date),0) FROM table1
WHERE 1 = 1
AND id = @id
AND id3 = 11
SELECT  @date2= MAX(date) FROM table1
WHERE 1 = 1
AND id = @id
AND id2 = 11


SELECT 
    'Warning' = CASE WHEN  CONVERT(TIME,GETDATE()) > '19:00:00' 
                     THEN  'not ok'
                END
FROM dbo.table1 AS a
INNER JOIN dbo.table AS a2 ON 1 = 1
AND @date1 != @date2
WHERE 1 = 1
AND a.nBranchId = @id
AND a.nInstrId = 11

Upvotes: 0

Views: 116

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

Not nearly equivalent. In both versions, the @date1 variable will be NULL if there are no rows satisfying the conditions of the query used to initialise its value.

If that's the case, the last select from version 1 will return a single row with value being either "Ok" or "Forwardpoints missing in XP_ResultsOutRightForwards", depending on the current time.

The version 2, however, will not return any rows whatsoever because you used this variable in the condition of an inner join. The inequality does not work with NULL values, so you will receive an empty set.

Upvotes: 1

Related Questions