CODEWITHSUNDEEP
sqlsql-serversql-server-2008case-when
Sreekumar P
Sreekumar P

Reputation: 6050

Are both queries the same?

Are both queries are same ? Do both return the same result ?

1)

IF EXISTS(
    SELECT 
        1 
    FROM 
        Users u 
    WHERE 
        u.UPIN = @AttendingDoctorID)
BEGIN
    SELECT 
        u.UserId, 1 
    FROM 
        Users u  WITH(nolock)
    WHERE 
        u.UPIN = @AttendingDoctorID
END ELSE BEGIN
    SELECT
        u.UserId,
        1
    FROM
        Users u (nolock)
    WHERE
        u.FirstName = @AttendingDoctorFirstName AND
        u.LastName = @AttendingDoctorLastName
END

2)

SELECT
u.UserId, 1
FROM
    Users u (nolock)
WHERE
    (u.UPIN = @AttendingDoctorID)
    OR
    (u.FirstName = @AttendingDoctorFirstName AND
    u.LastName = @AttendingDoctorLastName)

Upvotes: 3

Views: 82

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

Assuming you're running under the default transaction isolation level, you also need to be aware that:

IF EXISTS(
    SELECT 
        1 
    FROM 
        Users u 
    WHERE 
        u.UPIN = @AttendingDoctorID) --<-- Query 1
BEGIN
    SELECT 
        u.UserId, 1 
    FROM 
        Users u  WITH(nolock)
    WHERE 
        u.UPIN = @AttendingDoctorID  --<-- Query 2
END ELSE BEGIN
    SELECT
        u.UserId,
        1
    FROM
        Users u (nolock)
    WHERE
        u.FirstName = @AttendingDoctorFirstName AND
        u.LastName = @AttendingDoctorLastName
END

Another transaction might update Users between query 1 executing and query 2 executing, and so you might get an empty result set from query 2. Your second version runs everything as a single query, so will not have this issue (but others have pointed out other differences between the queries)

Upvotes: 1

Phil Gan
Phil Gan

Reputation: 2863

They're not semantically the same. The second query will possibly return records that fulfill both predicates (u.UPIN = @AttendingDoctorID) and (u.FirstName = @AttendingDoctorFirstName AND u.LastName = @AttendingDoctorLastName).

Whether or not this will ever occur depends on your data.

Upvotes: 2

gbn
gbn

Reputation: 432271

They are not the same.

  • The 2nd returns data for both conditions.
  • The 1st one tests first and applies only one condition

Upvotes: 4

Related Questions