cancel
Showing results for 
Search instead for 
Did you mean: 

Joining two tables

Former Member
0 Kudos

Hi,

I have two tables TableA and TableB which I need to join based on two columns and I need to get the values which are unique in both the tables.

TableA has values:

ColA     ColB

1001     A100

1002     B200

1003     C300

1004     D400

TableB has values:

ColA     ColB

1001     A100

1004     D400

1006     F600

1100     L100

I need to join TableA and TableB on both TableA.ColA = TableB.ColA and TableA.ColB = TableB.ColB and need to get the unique values from the both the tables like below

ColA     ColB

1002     B200

1003     C300

1006     F600

1100     L100

Is there any better way that I can do at the Data Foundation level and will have better query performance as well. Please help me by providing any feasable solution.

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hello,

I think you are expecting to compare using two column primary key and foreign key, which are nothing but composite key.

You can use index awareness in composite key scenario which will give output as per your requirement and also improves the performance.

While you define index awareness,

you have to concatenate both column in key definition as

column1||column2 for primary key and same for foreign key....

|| is used in oracle u can use any concatenate operator as per your database..

you can also refer following link.

how can we apply index awareness on composite primary key

Regards,

Priyank Bhayani

former_member4998
Active Contributor
0 Kudos

Hi


We can join two tables related by a single column primary key or foreign key pair

If you want to get the unique recoded from both the tables (A and B) then you can join Table A P.K = Table B F.K

Code1

Select

Col A,

Col B

From Table A, Table B

Where  TableA.ColA = TableB.ColA

Out Put:

ColA     ColB

1001     A100

1004     D400

Code 2

Select

Col A,

Col B

From Table A, Table B

Where  TableA.ColA = TableB.ColA

And     TableA.ColB = TableB.ColB

Out Put:

ColA     ColB

1001     A100

1004     D400

So you need to check the  joins  whether out put results getting correct results or not according to your business requirements.

Once you confirmed all the joins are working and getting the correct results, then for query performance you need to optimize the universe in different techniques. (like index , avoiding unnecessarily joins, etc...)