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 = @AttendingDo开发者_如何学编程ctorID
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)

---

They are not the same.

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

---

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.

---

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)