CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Sergey Metlov
Sergey Metlov

Reputation: 26331

Determine SELECTs are equivalent

Can you please help me to determine if these 2 SELECTs are equivalent?

SELECT sd.SESSION_DATA_ID
FROM SessionData sd
WHERE sd.DEVICE_TYPE = 'TRAC'
    AND (EXISTS
          (SELECT sr.ID
           FROM SessionResult sr
           LEFT JOIN BarcodeValues bv ON 
                sr.BARCODE_VALUE_0 = bv.ID AND 
                sr.SESSION_DATA_ID = sd.SESSION_DATA_ID AND
                sr.EVENT_NAME = 'Multi Full Cntr'
           WHERE
             bv.ENCRYPTED_VALUE LIKE '' OR bv.ENCRYPTED_VALUE IS NULL))

SELECT
    DISTINCT sd.SESSION_DATA_ID
FROM SessionData sd
LEFT JOIN SessionResult sr ON sd.SESSION_DATA_ID = sr.SESSION_DATA_ID
LEFT JOIN BarcodeValues bv ON bv.ID = sr.BARCODE_VALUE_0
WHERE
    sd.DEVICE_TYPE = 'TRAC' AND
    (sr.EVENT_NAME = 'Multi Full Cntr' AND
    bv.ENCRYPTED_VALUE LIKE '' OR bv.ENCRYPTED_VALUE IS NULL) OR
    sr.EVENT_NAME is NULL

Upvotes: 0

Views: 70

Answers (2)

paparazzo
paparazzo

Reputation: 45096

This makes it different

OR sr.EVENT_NAME is NULL

First will not match on sr.EVENT_NAME is NULL
Second will match on sr.EVENT_NAME is NULL

The second will match in two situations
- no match on sd.SESSION_DATA_ID = sr.SESSION_DATA_ID
- match on sd.SESSION_DATA_ID = sr.SESSION_DATA_ID and sr.EVENT_NAME is NULL

I suspect the first select is not doing what you think it is doing
The OR IS NULL in the WHERE will return rows where the join does not match
The following are NOT equivalent 

SELECT sr.ID
FROM SessionResult sr
LEFT JOIN BarcodeValues bv 
  ON  sr.BARCODE_VALUE_0 = bv.ID 
 AND  sr.SESSION_DATA_ID = sd.SESSION_DATA_ID 
 AND  sr.EVENT_NAME = 'Multi Full Cntr'
WHERE bv.ENCRYPTED_VALUE LIKE '' OR bv.ENCRYPTED_VALUE IS NULL

SELECT sr.ID
FROM SessionResult sr
JOIN BarcodeValues bv 
  ON  sr.BARCODE_VALUE_0 = bv.ID 
 AND  sr.SESSION_DATA_ID = sd.SESSION_DATA_ID 
 AND  sr.EVENT_NAME = 'Multi Full Cntr'
 AND (bv.ENCRYPTED_VALUE LIKE '' OR bv.ENCRYPTED_VALUE IS NULL)

I suspect this is what you mean / want is: 

SELECT distinct sd.SESSION_DATA_ID
FROM SessionData sd
JOIN SessionResult sr 
  ON sd.SESSION_DATA_ID = sr.SESSION_DATA_ID 
 AND(sr.EVENT_NAME = 'Multi Full Cntr' or sr.EVENT_NAME is NULL)
JOIN BarcodeValues bv 
  ON bv.ID = sr.BARCODE_VALUE_0
 AND(bv.ENCRYPTED_VALUE = '' OR bv.ENCRYPTED_VALUE IS NULL) 
WHERE sd.DEVICE_TYPE = 'TRAC'

AND is processed before OR

Operator Precedence (Transact-SQL)

  select 'yes' 
  where 1 = 0 and 0 = 1 or 1 = 1

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294407

No, they're not. The second one produces multiple rows for every match, because of added JOIN. You are trying to undo this by adding a DISTINCT, but that is not the same. If the SessionData.SESSION_DATA_ID is an unique key, then the results may match, but is not the same query because the fact that the results match with the first query is a side effect resulted from the actual values (uniqueness) not an inherent property of the request (query). QED.

Upvotes: 3

Related Questions