title: Odd LEFT JOIN error
body: <p>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:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Why am I getting an error on the outer join but not the inner join?</p>
  
  <p>P.S.: Fixing the data is <strong>not an option</strong>. It is not my data; I cannot touch it. I have to find a way to work with it.</p>
  
  <p>EDIT: I am running SQL Server 2008 R2 RTM</p>