SQL join on col1 if present else use col2

The problem here is that using a function or or around your join predicate means that the database engine can not use any indexes that you might have defined on the relevant columns for seeking so it is forced to table scan. On a large table this can take a long time. Worse, if this is part of a larger query you might find the scanning must be done multiple times.

If you have useful indexes on T2.col1 and T2.col2 (and T1.col1) then the following is likely to be more efficient, assuming that what you actually want is an INNER JOIN:

-- find matches for col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col2 AND T2.col1 IS NULL

Because each of the UNIONed SELECTs can avoid table scanning this will be more efficient than the single SELECT, assuming appropriate indexes exist for them to use.

If you do want a LEFT JOIN (i.e. you want to include rows from T1 that don't match those in T2 at all) then you need an extra clause for this:

-- find matches for col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col2 
          AND T2.col1 IS NULL -- this will match above and you'll get a duplicate
UNION ALL
-- find rows in T1 where there is no match in T2
SELECT *
  FROM T1
  LEFT JOIN 
       T2 ON T1.col1 = T2.col2 
 WHERE T2.col1 IS NULL
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

If you were not using SELECT * (i.e. had a fixed column list instead of the wildcard *) then you might find that third clause easier to understand as:

   SELECT [columns-to-project]
  FROM T1
 WHERE NOT EXISTS (SELECT * FROM T2 WHERE T2.col1 = t1.col1)
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

(whether this would be more or less efficient, or exactly the same, depends on your database's query planner and the indexes you have defined).

Note that the first SELECTs assume that you have no rows in T2 with a col2 that matches T1.col1 = T2.col2 and also have a col1 that matches T1.col1 = T2.col1, and that there are none with a valid T2.col2 but a T2.col1 whcih does not match any T1.col1. If such rows do exist then extra work is needed, I've not included that because your current queries would skip those matches just as my examples will.

Note also that any claims of efficiency of the above examples relies on appropriate indexes existing.

For a more concrete answer you will need to edit your question to provide:

• The table structure, particularly what indexes are defined.
• A more detailed description of your data, particularly with respect to what goes in T2.col1 & T2.col2 and when.
• What database system you are using (there might be specific features in your DB that could help with this sort of query and table design), unless you particularly need a database-agnostic answer.

I'd approach this problem by writing a SELECT statement on T2 which selected either col1 or col2, like:

SELECT  col_to_join = coalesce(col1, col2)
FROM    T2

Then use this SELECT statement in the join.