SQL Server : join on uniqueidentifier

Question

I have two tables Backup and Requests.

Below is the script for both the tables

Backup

CREATE TABLE UserBackup(
           FileName varchar(70) NOT NULL,
        )

File name is represented by a guid. Sometimes there is some additional information related to the file. Hence we have entries like guid_ADD entried in table.

Requests

CREATE TABLE Requests(
           RequestId UNIQUEIDENTIFIER NOT NULL,
           Status int Not null
        )

Here are some sample rows :

UserBackup table:

FileName
15b993cc-e8be-405d-bb9f-0c58b66dcdfe 
4cffe724-3f68-4710-b785-30afde5d52f8 
4cffe724-3f68-4710-b785-30afde5d52f8_Add
7ad22838-ddee-4043-8d1f-6656d2953545

Requests table:

RequestId                              Status
15b993cc-e8be-405d-bb9f-0c58b66dcdfe    1
4cffe724-3f68-4710-b785-30afde5d52f8    1
7ad22838-ddee-4043-8d1f-6656d2953545    2

What I need is to return all the rows from userbackup table whose name (the guid) is matches RequestId in the Requests table and the status is 1. So here is the query I wrote

Select * 
from UserBackup
inner join Requests on UserBackup.FileName = Requests.RequestId
where Requests.Status = 1

And this works fine. It returns me the following result

FileName                                      RequestId                              Status
15b993cc-e8be-405d-bb9f-0c58b66dcdfe          15b993cc-e8be-405d-bb9f-0c58b66dcdfe     1
4cffe724-3f68-4710-b785-30afde5d52f8          4cffe724-3f68-4710-b785-30afde5d52f8     1
4cffe724-3f68-4710-b785-30afde5d52f8_Add      4cffe724-3f68-4710-b785-30afde5d52f8     1

This is exactly what I want. But what I don't understand is how it is working. If you notice the result is returning 4cffe724-3f68-4710-b785-30afde5d52f8_Add row as well. The inner join is on varchar and uniqueidentifier, and this join instead of working like "Equals to" comparison works like "contains" comparison. I want to know how this works so that I can be sure to use this code without any unexpected scenarios.

Solution

The values on both sides of a comparison have to be of the same data type. There's no such thing as, say, comparing a uniqueidentifier and a varchar.

uniqueidentifier has a higher precedence than varchar so the varchars will be converted to uniqueidentifiers before the comparison occurs.

Unfortunately, you get no error or warning if the string contains more characters than are needed:

select CONVERT(uniqueidentifier,'4cffe724-3f68-4710-b785-30afde5d52f8_Add')

Result:

4CFFE724-3F68-4710-B785-30AFDE5D52F8

If you want to force the comparison to occur between strings, you'll have to perform an explicit conversion:

Select * 
from UserBackup
inner join Requests
on UserBackup.FileName = CONVERT(varchar(70),Requests.RequestId)
where Requests.Status = 1