Full Outer Join
Let us start our blog knowledge with Database [That was my first step into the world of software !].
During my days at one of the consulting companies, i was working as a Data conversion specialist, additional to my Java Lead role [Its always like this in consulting, you will be given huge amount of work with less time to complete them uhhh..] One of our business requirements was to get the records from two tables, together. The is a column which is present in both table A & B.
i.e Table A had Col1 & Table B also had Col1.
Now, when entering data, Table A will have some rows, Table B will have some rows. We have to query both the tables, get the records and show them in same line if they both have same data for Col1 in their tables.
OK, in normal world, i would have been thinking of putting two outer joins and then integrating the results in the Java using some crap logic. This is due to the fact that an outer join extends the result of an inner join by including rows from one table (say Table A) that don't have corresponding rows in another table (say Table B). An important thing to note here is that the outer join operation will not include the rows from Table B that don't have corresponding rows in Table A. In other words, an outer join is unidirectional. But there are situations when you may want a bidirectional outer join, i.e., you want to include all the rows from A and B:
1. Rows from the result of the inner join from both the tables
2. Rows from B that don't have corresponding rows in table A
3. Rows from A that don't have corresponding rows in table B
But believe me, if you know how to google, you get your result faster and most importantly, you can avoid reinventing the wheel
i was just googling for ways to get data from both the tables and was getting various results on FULL OUTER JOIN.
I found the simplest example of FULL OUTER JOIN in Orielly site
http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html
FULL OUTER JOIN - This works on Oracle DB from version 9i & above.
Simple usage of the query with the above said scenario
> Select A.col1, A.col2,B.col3 from A FULL OUTER JOIN on B where A.col1=B.col1
Bingo, i ended up writing a single query thereby optimizing the network traffic to DB and with no coding on Java side. Just get the resultset, iterate & show.
During my days at one of the consulting companies, i was working as a Data conversion specialist, additional to my Java Lead role [Its always like this in consulting, you will be given huge amount of work with less time to complete them uhhh..] One of our business requirements was to get the records from two tables, together. The is a column which is present in both table A & B.
i.e Table A had Col1 & Table B also had Col1.
Now, when entering data, Table A will have some rows, Table B will have some rows. We have to query both the tables, get the records and show them in same line if they both have same data for Col1 in their tables.
OK, in normal world, i would have been thinking of putting two outer joins and then integrating the results in the Java using some crap logic. This is due to the fact that an outer join extends the result of an inner join by including rows from one table (say Table A) that don't have corresponding rows in another table (say Table B). An important thing to note here is that the outer join operation will not include the rows from Table B that don't have corresponding rows in Table A. In other words, an outer join is unidirectional. But there are situations when you may want a bidirectional outer join, i.e., you want to include all the rows from A and B:
1. Rows from the result of the inner join from both the tables
2. Rows from B that don't have corresponding rows in table A
3. Rows from A that don't have corresponding rows in table B
But believe me, if you know how to google, you get your result faster and most importantly, you can avoid reinventing the wheel
i was just googling for ways to get data from both the tables and was getting various results on FULL OUTER JOIN.
I found the simplest example of FULL OUTER JOIN in Orielly site
http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html
FULL OUTER JOIN - This works on Oracle DB from version 9i & above.
Simple usage of the query with the above said scenario
> Select A.col1, A.col2,B.col3 from A FULL OUTER JOIN on B where A.col1=B.col1
Bingo, i ended up writing a single query thereby optimizing the network traffic to DB and with no coding on Java side. Just get the resultset, iterate & show.
Comments