Using CHECKSUM on where clause
Nobody can really tell you exactly why this person coded the WHERE clause this way. I can tell you that I can't think of a reason that form is better than:

r.roomprovider_id &lt;&gt; br.roomprovider_id


(And while I can interpret how the code is currently written, I am not sure of the expected/intended behavior - should a row be included or excluded when the right side is NULL?)

My guesses for their motivation include:


job security through obfuscation
job security through forcing bad performance, that they could fix later easily, since this will remove the possibility to seek on any of those columns if they are suitably indexed to satisfy the query
CHECKSUM() was a shiny new function they just learned, and they just had to implement it somewhere
they believed that CHECKSUM() was less prone to inaccuracy from collisions than other methods, or that - with only one input - it served any purpose whatsoever