Using 'in' in Join

Question

i have two selects a & b and i join them like:

select * from 
(
   select n.id_b || ',' || s.id_b ids, n.name, s.surname 
   from names n, 
        surnames s where n.id_a = s.id_a
) a 
left join
(
    select sn.id, sn.second_name
) b on b.id in (a.ids)

in this case join doesn't work :( The problem is in b.id in (a.ids). But why if it looks like 12 in (12,24) and no result :(

Answer 1

This won't work because (12,24) is a single discrete string not a comma-separated set of numbers. Clearly, 12 != '(12,24)', hence no results are returned.

edit

I missed the outer join in your posted query. So you ought to get something back, even though there is no join between the two tables. Here is some test data:

SQL> select * from names
  2  /

      ID_A       ID_B NAME
---------- ---------- ----------
         1         12 SAM

SQL> select * from surnames
  2  /

      ID_A       ID_B SURNAME
---------- ---------- ----------
         1         24 I-AM

SQL> select * from whatever
  2  /

        ID SECOND_NAM
---------- ----------
        24 I AM SAM

SQL>

My query is similar to yours except I cast b.id as a string because a.ids is a string. If I don't do this, the query fails with ORA-1722: invalid number.

SQL> select * from
  2      (
  3           select n.id_b || ',' || s.id_b ids, n.name, s.surname
  4           from names n,
  5                surnames s
  6           where n.id_a = s.id_a
  7      ) a
  8  left join
  9      (
 10          select sn.id, sn.second_name
 11          from whatever sn
 12      ) b on to_char(b.id) in (a.ids)
 13  /

IDS        NAME       SURNAME            ID SECOND_NAM
---------- ---------- ---------- ---------- ----------
12,24      SAM        I-AM

SQL>

As you can see, it returns values from the left-hand query and nothing from the right, for the reason which I gave above.

If you want to get something from both queries on the basis on partial matching of the IDs you need to do this:

SQL> select a.ids
  2         , a.name
  3         , a.surname
  4         , b.id
  5         , b.second_name
  6  from
  7      (
  8           select n.id_b || ',' || s.id_b ids
  9                  , n.name
 10                  , s.surname
 11                  , n.id_b as n_id_b
 12                  , s.id_b as s_id_b
 13           from names n,
 14                surnames s
 15           where n.id_a = s.id_a
 16      ) a
 17  left join
 18      (
 19          select sn.id, sn.second_name
 20          from whatever sn
 21      ) b on (b.id = a.n_id_b or b.id = a.s_id_b )
 22  /

IDS        NAME       SURNAME            ID SECOND_NAM
---------- ---------- ---------- ---------- ----------
12,24      SAM        I-AM               24 I AM SAM

SQL>

If you take this latter approach you may want to consider turning the outer join into an inner. Depnds on you precise business rule.