0

There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup.

What I want is either join on T1.col1 = T2.col1 or T1.col1 = T2.col2 if col1 is NULL in T2.

I have already tried these:

select * from T2 left join T1
on T1.col1 = coalesce(T2.col1, T2.col2)
select * from T2 left join T1
on T1.col1 in (T2.col1, T2.col2)
select * from T2 left join T1
on T1.col1 = T2.col1
or T1.col1 = T2.col2)

Which result in the ETL job never ending.

Additional info:

  • Redshift DB
  • No indexes because columnar DB
  • T2.col1 and T2.col2 will be ditto. T2.col1 comes from upstream, whereas T2.col2 is derived from the ETL. Until upstream quality improves, we'll need fallback on col2.
3

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.
| improve this answer | |
  • Thank you for the detailed answer. 1) No index that I'm aware of 2) T2.col1 and T2.col2 are the same in every aspect, except that T2.col1 may be null sometimes. Until this column is consistently being populated, we will need to fallback to T2.col2 which is on its way to deprecation 3) The database is on Redshift – Theja Sep 3 at 15:12
-1

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.

| improve this answer | |
  • 2
    Apart from being non-standard SQL, this is the same as the join expression on T1.col1 = coalesce(T2.col1, T2.col2) used in the question – a_horse_with_no_name Sep 2 at 5:49
  • This is basically a design flaw If you absolutely have to work this way coalesce the data from 2 columns to 1 into a temp table and add the relevant indexes. Then join after that - that way you will only get bad performance - not catastrophically awful. – Mo64 Sep 2 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.