CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Ethan Reesor
Ethan Reesor

Reputation: 2180

Odd LEFT JOIN error

I have two tables I am trying to join together on a particular column (shared between the two). The data in these columns are supposed to represent numbers, but the data are actually characters, and some of the values are non-numeric (e.g. '2,3,4', or 'n/a'). I am ignoring the rows with non-numeric values of this column. I am treating the columns as numeric in the join because '001' must match '1', '01', '0001', etc. Inner joining them works, left outer joining them doesn't:

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
    ON T1.ID NOT LIKE '%[^ 0-9]%'
    AND T2.ID NOT LIKE '%[^ 0-9]%'
    AND T1.ID + 0 = T2.ID + 0
-- Success!

SELECT *
FROM Table1 T1
LEFT JOIN Table2 T2
    ON T1.ID NOT LIKE '%[^ 0-9]%'
    AND T2.ID NOT LIKE '%[^ 0-9]%'
    AND T1.ID + 0 = T2.ID + 0
-- Conversion failed when converting the nvarchar value '2,3,4' to data type int.

Why am I getting an error on the outer join but not the inner join?

P.S.: Fixing the data is not an option. It is not my data; I cannot touch it. I have to find a way to work with it.

EDIT: I am running SQL Server 2008 R2 RTM

Upvotes: 1

Views: 483

Answers (2)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

SQL Server does not guarantee the order in which the conditions will be evaluated. In your case, T1.ID + 0 = T2.ID + 0 is being evaluated before the NOT LIKE conditions.

Please try the following (SQL 2012 and above):

SELECT *
FROM Table1 T1
LEFT JOIN Table2 T2
    ON TRY_CAST(T1.ID AS int) = TRY_CAST(T2.ID AS int)

SQL 2008:

SELECT *
FROM (SELECT * FROM Table1 WHERE ID NOT LIKE '%[^ 0-9]%') T1
LEFT JOIN (SELECT * FROM Table2 WHERE ID NOT LIKE '%[^ 0-9]%') T2
    ON CAST(T1.ID AS INT) = CAST(T2.ID AS INT)

Reference

Upvotes: 2

Fredou
Fredou

Reputation: 20100

what if you try this instead? does it do what you want?

SELECT *
FROM Table1 T1
LEFT JOIN Table2 T2
   ON (isnumeric(T1.ID) = 1
      AND  isnumeric(T2.ID) = 1)
      AND try_parse(T1.ID as int) + 0 = try_parse(T2.ID as int) + 0

Upvotes: 1

Related Questions