Oracle join multiple rows

Question

I've two tables, one contains ID <-> name mapping and the other table contains more than one ID column. To list out the records of the second table with the corresponding names of the IDs, I've a query like

SELECT 
 (SELECT NAME FROM TAB2 WHERE ID = ID1 ),
 (SELECT NAME FROM TAB2 WHERE ID = ID2),
 .
 .
 .
FROM TAB1

Would this be the only way to do it? as the second table is queried for every ID column for every row.

show both table structures and explain the query with required results. — manurajhada, Jul 03 '12 at 09:23
Looks good to me. Since the ID's can be different, you'd need multiple lookups to tab2 however you phrase the query. — Andomar, Jul 03 '12 at 09:24

score 2 · Answer 1 · answered Jul 03 '12 at 09:27

2

You can try this approach:

select t1.*
from Table1 t1
join Table2 t2 on t1.id=t2.id1 OR t1.id=t2.id2 OR t1.id=t2.id3

Each row of Table1 for which there exists a row of Table2 such that any of its ids matches that of Table1 will be returned.

answered Jul 03 '12 at 09:27

Sergey Kalinichenko

714,442
84
1,110
1,523

Based on the OP's query, it's `Table1` that has multiple ID columns and `Table2` is the lookup table, although the idea should be clear anyway. Shame this query won't return the same results, though. – Andriy M Jul 03 '12 at 17:31

score 0 · Answer 2 · edited May 23 '17 at 12:20

As an option, you could try rewriting your query like this:

SELECT 
 b1.NAME,
 b2.NAME,
 .
 .
 .
FROM TAB1 a
INNER JOIN TAB2 b1 ON a.ID1 = b1.ID
INNER JOIN TAB2 b2 ON a.ID1 = b1.ID
...

That will hardly make any difference in term of efficiency, but it at least might make your query more readable (which is subjective, of course).

There might be another option to consider but there are two big IFs I am not sure about: if it is applicable at all in your situation and if it will be more efficient.

The first one depends on the presence of a key in TAB1. If there's no dedicated key column, it would be enough if all the combinations of (ID1, IF2, ... IDN) were guaranteed to be unique. And whether this approach can be more efficient than your multiple lookups depends on the number of the IDx columns.

The idea is to join the two table just once on a condition similar to the one in @dasblinkenlight's answer, then group by the key column(s) and pull the names using conditional aggregation. Here's what I mean:

SELECT
  MAX(CASE TAB2.ID WHEN TAB1.ID1 THEN TAB2.NAME END) AS NAME1,
  MAX(CASE TAB2.ID WHEN TAB1.ID2 THEN TAB2.NAME END) AS NAME2,
  .
  .
  .
FROM TAB1
INNER JOIN TAB2 ON TAB2.ID IN (TAB1.ID1, TAB1.ID2, ...)
GROUP BY TAB1.ID1, TAB1.ID2, ...

Oracle join multiple rows

2 Answers2