Joining Different Data Types

Question

Is there any way to compare two different types of data?

For example, I'm trying to use two different columns to LEFT JOIN two tables A and B. The key for table A is ProductNumber, which has been entered into the database (that I cannot edit) as a text, and the key for table B is StoreProductNumber which is number data.

If I use a LEFT JOIN b ON a.ProductNumber LIKE b.StoreProductNumber will this allow me to use the two for JOINING?

Previously, when I have tried in Access and SSMS, I have received an error mentioning how the two columns are made of different types of data.

Answer 1

If ProductNumber can contain MORE than the StoreProductNumber:

LEFT JOIN b 
ON a.ProductNumber LIKE '%' + CAST(b.StoreProductNumber as varchar(n)) + '%'

Obviously you can modify n to make sure your StoreProductNumber isn't truncated.

However, if they're guaranteed to be the SAME (just different datatypes), you can just compare them directly:

LEFT JOIN b
on CAST(a.ProductNumber as BIGINT) = b.StoreProductNumber

And if you don't need a BIGINT, you can use an INT or whatever datatype you require.

Lastly, as HLGEM pointed out, SQL will do implicit conversions for you, so technically this would also work:

LEFT JOIN b
on a.ProductNumber = b.StoreProductNumber

But, I prefer to do all conversions explicitly for clarity, so I suggest against this approach.

Answer 2

In SQL server you can join on different datatypes if the datatypes can implicitly convert and if the columns do not contain any data that would be a problem converting. For reference on what datatypes implicitly convert, see:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine

To test this try the following:

 Create table #temp (test int)
 insert #temp values(1), (2)

 Create table #temp2  (test bigint)
 insert #temp2 values(1), (2)

 select * from #temp t join #temp2 t2 on t.test = t2.test

 Create table #temp3 (test int)
 insert #temp3 values(1), (2)

 Create table #temp4 (test varchar(10))
 insert #temp4 values('1'), ('test')

 select * from #temp3 t3 join #temp4 t4 on t3.test = t4.test

 Create table #temp5 (test int)
 insert #temp5 values(1), (2)

 Create table #temp6 (test varchar(10))
 insert #temp6 values('1'), ('2')

 select * from #temp5 t5 join #temp6 t6 on t5.test = t6.test

While you can do implicit conversions, you can also explicitly convert which may improve clarity in maintenance. But if there is a problem converting, explicitly casting will have the same results as an implicit cast:

 Create table #temp7 (test int)
 insert #temp7 values(1), (2)

 Create table #temp8 (test varchar(10))
 insert #temp8 values('1'), ('test')

 select * from #temp7 t7 join #temp8 t8 on t7.test = cast(t8.test as int)

I will point out that if the two fields are supposed to contain the same type of data, the best solution is to fix the mismatch. In a legacy system with lots of possible code problems if you change or where you are pulling from two disparate databases managed by different teams or if one of the databases is a COTS (commercial Off-the-Shelf) product and you cannot make changes without messing up your license, of course you can't do this. But if you can, it is worth your while to fix it before you end up with the database doing a lot of unnecessary work converting.

Answer 3

Convert storeProductNumber to VARCHAR and then JOIN

SELECT * FROM a 
LEFT JOIN b ON a.ProductNumber =  CAST(b.StoreProductNumber as VARCHAR(MAX))