Search code examples
sqlsql-servermariadbinner-join

Matching on Values of Different Types in Two SQL DBs

I am reworking some old SQL queries that were written by others, so that they're now run in a Node environment (I am more familiar with JS than I am with SQL), and I cam across this query:

   UPDATE LWA
    SET
    DC.ACTINDX = TR00100.ACTINDX,
    DC.ACTNUMBR_1 = TR00100.ACTNUMBR_1,
    DC.ACTNUMBR_2 = TR00100.ACTNUMBR_2
    FROM [LWA].dcr..dcr_accounts LWA
    INNER JOIN MCDB..TR00100 ON LWA.ACTINDX = TR00100.ACTINDX
    WHERE ACCNUM = '32'

Notice this line here: LWA.ACTINDX = TR00100.ACTINDX

In looking at the two databases I see that these two columns that are being used for the INNER JOIN are of different types. LWA.ACTINDX is of type varchar(15) and TR00100.ACTINDX is of type INT.

So, that said, my understanding is this wouldn't work, correct? In other words even if the values are the same, if they're of different types a match will never be found. Is that correct, or does some kind of type coercion happen in this case? And to clarify, one db is MariaDB, and the other is SQL Server.

Solution

  • SQL Server -- as with all databases -- will do implicit type conversion.

    In this case, the string is converted to an integer (or number or some sort). If it cannot be converted, you will get a type conversion error.

    Note that such type conversions often significantly slow down queries because they often impede the use of indexes.