title: MySQL: COALESCE within JOIN
body: <p>I've been trying to create a query using COALESCE with multiple forms of matching in order to join two tables -- but as far as I can tell, it hasn't been working.</p>
  
  <p>Can somebody tell me what's wrong with this query?</p>
  
  <pre><code>SELECT *
  FROM t1
  LEFT JOIN t2 ON COALESCE(t1.id,t1.phone,t1.address) = COALESCE(t2.id,t2.phone,t2.address)
  </code></pre>
  
  <p>Something like that. The hope would be that the query would look to see if the unique IDs in <code>t1</code> and <code>t2</code> matched first, and if they didn't, it would move on to see if the phones matched, etc. It would be very helpful to attempt to match on multiple sets of criteria and to return the ones that matched any of the column and to only return the NULLs from <code>t1</code> if the query couldn't find a match at all.</p>
  
  <hr>
  
  <p>Edit:</p>
  
  <p>By "not working," I meant that it seems like the ID matching works -- where it will return the data from <code>t2</code> (and not NULLs) if the unique identifiers matched, but it doesn't move on to attempt to match on phone number or address line; this is obviously likely because the <code>t1</code> table isn't returning any NULL values.</p>
  
  <p>The definitions of the table is that <code>t1</code> is a smaller subset of data that likely lives in the larger <code>t2</code> table. Let's say that <code>t1</code> is a table of about 100 people with only a few criteria: name, phone, address, id (though ID doesn't exist in every row) -- whereas <code>t2</code> is a larger table of about 30,000 with much more criteria (name, phone, address, id, job, volunteer, email, notes, etc.) where I'm trying to find the 100 within the 30k.</p>