I have two tables with below structures:

create table TABLE_LC
( lc_num   NUMBER,
  brn_code NUMBER,
  brn_role NUMBER)

create table BRN
( brn_code NUMBER,
  brn_name VARCHAR2(50))

Some example data for each table is this:


lc_num    |  brn_code  |  brn_role
  1       |     1      |  1010  
  1       |     2      |  2020  


brn_code  |  brn_name  
  1       |   BAHAR    
  2       |    KAJ     

I need To have one record for each lc_num in TABLE_LC and the desired output based on the given tables and data is this:

lc_num    |  Advising_Bank  |  Issuing_Bank
  1       |     BAHAR       |  KAJ 

What I've written is below query which gives me the correct output,considering the fact that we know brn_role=1010 represents Advising Bank and brn_role=2020 represents Issuing Bank

select t1.lc_num, 
       t2.brn_name "Advising Bank", 
       t4.brn_name "Issuing bank"
  from TABLE_LC t1
 inner join BRN t2
    on t1.brn_code = t2.brn_code
   and t1.brn_role = 1010
 inner join TABLE_LC t3
    on t3.lc_num = t1.lc_num
 inner join BRN t4
    on t4.brn_code = t3.brn_code
   and t3.brn_role = 2020

As you can see, I have joined the base table twice, I want to know if there are better ways of getting the desired output ?

Thanks in advance

  • 1
    There is a technique which allows to use one BRN table copy - but it cannot be referred to as "better way" (it uses grouping and conditional aggregation). The query which you use now is optimal, especially when it is indexed optimally. – Akina Oct 6 '20 at 8:24
  • @Akina Thanks. I thought that the way I used is not that professional and there must be better ways. Thanks for your answer. – Pantea Oct 6 '20 at 8:31
  • 1
    :) pre-optimization is evil. – Akina Oct 6 '20 at 8:33

Better? It depends.

If the primary key of TABLE_LC is (lc_num, brn_role) then you could do something like this:

        WHEN brn_role = 1010 THEN brn_name
        ELSE NULL
    ) AS Advising_Bank
        WHEN brn_role = 2020 THEN brn_name
        ELSE NULL
    ) AS Issuing_Bank
      BRN BRN
        ON BRN.brn_code = LC.brn_code
  ) x

Which, you'll note:

  1. Is about the same amount of code
  2. Is harder to understand
  3. Even though it only performs one join, it still might not produce the best query plan, depending on whether or not TABLE_LC is index-organized.

You could also use PIVOT, but again, probably not that much time savings and you're left with the problem of dealing with goofy column names.

Joins on primary keys/appropriate indexes are generally not that expensive, so avoiding them only makes sense in certain situations.


So I changed my query to this and it gives me the result I want :

select t1.lc_num, 
       t2.brn_name "Advising Bank", 
       t3.brn_name "Issuing bank"
  from TABLE_LC t1
 left join BRN t2
    on t1.brn_code = t2.brn_code
   and t1.brn_role = 1010
 left join BRN t3
    on t1.brn_code = t3.brn_code
   and t1.brn_role = 2020

Actually there is no need to have another inner join TABLE_LC. Instead I changed all inner joins to left join and I think this approach is better than the others, performance wise.

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.