on 05-05-2014 2:39 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.