1

I have two tables each with the following id columns:

Ticker
SEDOL,
ISIN

Each row has one or more of these columns populated with a value. I would like to join on whichever column has a value. Does anyone know how to join this way? Thanks.

joar
  • 15,077
  • 1
  • 29
  • 54
user2584854
  • 33
  • 3

3 Answers3

3

Ticker, SEDOL and ISIN are all different formats so I assume you need

SELECT *
FROM   T1
       JOIN T2
         ON T1.Ticker = T2.Ticker
             OR T1.SEDOL = T2.SEDOL
             OR T1.ISIN = T2.ISIN

Performance will be poor if the tables are large however, See Is having an 'OR' in an INNER JOIN condition a bad idea?.

if both tables are consistent on the columns that are supplied for a particular security then this will potentially be much faster as it can use a hash or merge join not just nested loops.

SELECT *
FROM   T1
       INNER JOIN T2
         ON EXISTS (SELECT T1.Ticker,
                           T1.SEDOL,
                           T1.ISIN
                    INTERSECT
                    SELECT T2.Ticker,
                           T2.SEDOL,
                           T2.ISIN)

Or if the tables are not consistent then another option might be

SELECT *
FROM   T1
       INNER JOIN T2
         ON T1.Ticker = T2.Ticker
UNION
SELECT *
FROM   T1
       INNER JOIN T2
         ON T1.SEDOL = T2.SEDOL
UNION
SELECT *
FROM   T1
       INNER JOIN T2
         ON T1.ISIN = T2.ISIN
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • This solution works but performs poorly as the two tables are both 20,000+ records. – user2584854 Aug 12 '13 at 18:32
  • Is there a way to only join on a field if it exists in both tables T1 and T2? Would that speed things up? – user2584854 Aug 12 '13 at 20:34
  • Martin - thanks for your suggestion however the union and the intersect snippits above actually run slower than the first post (regular join). – user2584854 Aug 13 '13 at 01:39
  • What indexes do you have on the tables? What do the execution plans look like? What join type are you getting for the `INTERSECT` query? If still nested loops maybe try `INNER HASH JOIN` and see if that improves things. – Martin Smith Aug 13 '13 at 06:56
0

You can use the ISNULL key word in your ON part.

Select *
From tab1
inner join tab2 on tab1.ColName = Isnull(Isnull(tab2.Ticker,tab2.SEDOL),tab2.ISIN)

Added after a comment: This query is done assuming you have a value in one column in one table, and in the other you have 3 columns where always 2 are null and one has a value that should match the column in the first table. The ISNULL is used to select the correct value from the 3 values.

asafrob
  • 1,838
  • 13
  • 16
  • SQL server has coalesce function which is basically isnull, but can take more than two parameters – Roman Pekar Aug 10 '13 at 05:50
  • Thanks for the suggestion, but not sure how this would work... tab1.ColName is a specific column say ISIN. It tab2.col1 is null and we use tab2.col2 (say SEDOL) it would never match right? – user2584854 Aug 12 '13 at 15:55
  • @user2584854 - added an explanation it the post itself – asafrob Aug 12 '13 at 16:00
0
select *
from T1
    inner join T2 on T2.Ticker = coalesce(T1.Ticker, T1.SEDOL, T1.ISIN)

but if it possible that value in T1 is not null, but T2 doesn't have a corresponding value, you can do

select *
from T1
    outer apply
    (
        select top 1 T2.Col
        from T2
        where T2.Ticker in (T1.Ticker, T1.SEDOL, T1.ISIN)
        order by
             case
                 when T2.Ticker = T1.Ticker then 0
                 when T2.Ticker = T1.SEDOL then 1
                 when T2.Ticker = T1.ISIN then 2
             end
    )

or you can do

select *, coalesce(T21.Col, T22.Col, T23.Col) as T2_Col
from T1
    left outer join T21 on T21.Ticker = T1.Ticker
    left outer join T22 on T22.Ticker = T1.SEDOL
    left outer join T23 on T23.Ticker = T1.ISIN
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197