CREATE TABLE table_join_left ( id1 NUMBER(2,0) , desc1 VARCHAR(20) );
CREATE TABLE table_join_right ( id2 NUMBER(2,0) , desc2 VARCHAR(20) );
INSERT INTO table_join_left (id1, desc1) VALUES (11, 'table_join_left_11'); INSERT INTO table_join_left (id1, desc1) VALUES (12, 'table_join_left_12'); INSERT INTO table_join_left (id1, desc1) VALUES (13, 'table_join_left_13'); INSERT INTO table_join_right (id2, desc2) VALUES (11, 'table_join_right_11'); INSERT INTO table_join_right (id2, desc2) VALUES (12, 'table_join_right_12'); INSERT INTO table_join_right (id2, desc2) VALUES (14, 'table_join_right_13'); COMMIT;Understanding Join Mechanics:
table_join_left
and table_join_right
can be visualized as:for(ROW RowL : table_join_left) { // outer loop: RowL represents a row of the current iteration of the outer loop on table table_join_left for(ROW RowR : table_join_right) { // inner loop: RowR represents a row of the current iteration of the inner loop on table table_join_right // Join condition evaluation and result processing occurs here } }Note:
SELECT * FROM table_join_left tj1 JOIN table_join_right tj2 ON tj2.id2 = tj1.id1Query result:
ID1 DESC1 ID2 DESC2 --- ------------------ --- ------------------- 11 table_join_left_11 11 table_join_right_11 12 table_join_left_12 12 table_join_right_12How INNER JOIN Works:
table_join_left
).table_join_right
).table_join_left
and table_join_right
can be seen as follows:for(ROW RowL : table_join_left) { // outer loop for(ROW RowR : table_join_right) { // inner loop if(CONDITION(RowL, RowR)) { // join condition // keep the rows: RowL and RowR } } }Note:
SELECT * FROM table_join_left tj1 LEFT JOIN table_join_right tj2 ON tj2.id2 = tj1.id1Query result:
ID1 DESC1 ID2 DESC2 ------ ------------------ ------ ------------------- 11 table_join_left_11 11 table_join_right_11 12 table_join_left_12 12 table_join_right_12 13 table_join_left_13 (null) (null)How LEFT OUTER JOIN Works:
table_join_left
).table_join_right
).table_join_left
and table_join_right
can be seen as follows:for(ROW RowL : table_join_left) { // outer loop match = false; for(ROW RowR : table_join_right) { // inner loop if(CONDITION(RowL, RowR)) { // join condition // keep the rows: RowL and RowR match = true; } } if(match == false) { // no match // initialize a new empty row from table table_join_right: RowR_NULL // initialize the columns of the new row to NULL // keep the rows: RowL and RowR_NULL } }Note:
SELECT * FROM table_join_left tj1 RIGHT JOIN table_join_right tj2 ON tj2.id2 = tj1.id1Query result:
ID1 DESC1 ID2 DESC2 ------ ------------------ ------ ------------------- 11 table_join_left_11 11 table_join_right_11 12 table_join_left_12 12 table_join_right_12 (null) (null) 14 table_join_right_13How RIGHT OUTER JOIN Works:
table_join_right
).table_join_left
).table_join_left
and table_join_right
can be seen as follows:for(ROW RowR : table_join_right) { // outer loop match = false; for(ROW RowL : table_join_left) { // inner loop if(CONDITION(RowL, RowR)) { // join condition // keep the rows: RowL and RowR match = true; } } if(match == false) { // no match // initialize a new empty row from table table_join_left: RowL_NULL // initialize the columns of the new row to NULL // keep the rows: RowL_NULL and RowR } }Note:
SELECT * FROM table_join_left tj1 FULL JOIN table_join_right tj2 ON tj2.id2 = tj1.id1Query result:
ID1 DESC1 ID2 DESC2 ------ ------------------ ------ ------------------- 11 table_join_left_11 11 table_join_right_11 12 table_join_left_12 12 table_join_right_12 13 table_join_left_13 (null) (null) (null) (null) 14 table_join_right_13How FULL OUTER JOIN Works:
table_join_left
).table_join_right
).table_join_left
and table_join_right
can be seen as follows:for(ROW RowL : table_join_left) { // outer loop match = false; for(ROW RowR : table_join_right) { // inner loop if(CONDITION(RowL, RowR)) { // join condition // keep the rows: RowL and RowR match = true; } } if(match == false) { // no match // initialize a new empty row from table table_join_right: RowR_NULL // initialize the columns of the new row to NULL // keep the rows: RowL and RowR_NULL } } // Handle unmatched rows from table table_join_right for(ROW RowR : table_join_right) { // loop through table table_join_right match = false; for(ROW RowL : table_join_left) { // check against table table_join_left if(CONDITION(RowL, RowR)) { // join condition match = true; break; // already processed in first loop } } if(match == false) { // no match // initialize a new empty row from table table_join_left: RowL_NULL // initialize the columns of the new row to NULL // keep the rows: RowL_NULL and RowR } }Note:
SELECT * FROM table_join_left tj1 CROSS JOIN table_join_right tj2Query result:
ID1 DESC1 ID2 DESC2 --- ------------------ --- ------------------- 11 table_join_left_11 11 table_join_right_11 11 table_join_left_11 12 table_join_right_12 11 table_join_left_11 14 table_join_right_13 12 table_join_left_12 11 table_join_right_11 12 table_join_left_12 12 table_join_right_12 12 table_join_left_12 14 table_join_right_13 13 table_join_left_13 11 table_join_right_11 13 table_join_left_13 12 table_join_right_12 13 table_join_left_13 14 table_join_right_13How CROSS JOIN Works:
table_join_left
).table_join_right
).table_join_left
and table_join_right
can be seen as follows:for(ROW RowL : table_join_left) { // outer loop for(ROW RowR : table_join_right) { // inner loop // keep the rows: RowL and RowR } }Note: