Difference between revisions of "OUTER JOIN"

From Robs_Wiki
Jump to: navigation, search
(Created page with "__TOC__ == Introduction == Instead of exclusively returning results which satisfy all join conditions of an INNER JOIN, an OUTER JOIN returns not only results which satisfy a...")
 
 
Line 55: Line 55:
 
         3 YELLOW    BMW
 
         3 YELLOW    BMW
 
</pre>
 
</pre>
 +
== Difference between full outer join and cartesian join ==
 +
* A Full outer join will fetch at maximum 'addition of 2 tables' where as the Cartesian product will fetch the 'product of 2 tables'. Ex: Table A - 2 rows; Table B - 3 rows.
 +
** A full outer join will fetch in 2+3 = 5 rows.
 +
** A Full outer join will fetch in 2x3 = 6 rows

Latest revision as of 09:53, 29 January 2020

Introduction

Instead of exclusively returning results which satisfy all join conditions of an INNER JOIN, an OUTER JOIN returns not only results which satisfy all conditions, but also returns rows from one table which did not satisfy the condition. The table that is chosen for this “bypass” of conditional requirements is determined by the directionality or “side” of the join, typically referred to as LEFT or RIGHT outer joins.

When defining a side to your OUTER JOIN, you are specifying which table will always return its row even if the opposing table on the other side of the join has missing or null values as part of the joining condition.

Example Outer Join using Oracle syntax

-- consider the following tables
SQL> select * from robla.test1;

      CODE COLOUR
---------- ----------
         1 GREEN
         2 BLUE
         3 YELLOW

SYS @ TSTDB [T52-d1-db2-10g-sup] : 
SQL> select * from robla.test2;

      CODE CAR
---------- ------------------------------
         1 VOLVO
         3 BMW
         4 VOLKSWAGEN
         5 AUDI
-- an inner join would only show the rows that are present in both tables
select a.code, a.colour, b.car from robla.test1 a, robla.test2 b where a.code = b.code order by a.code;

      CODE COLOUR     CAR
---------- ---------- ------------------------------
         1 GREEN      VOLVO
-- consider both outer join examples:

SQL> select a.code, a.colour, b.car from robla.test1 a, robla.test2 b where a.code(+) = b.code order by a.code;

      CODE COLOUR     CAR
---------- ---------- ------------------------------
         1 GREEN      VOLVO
         3 YELLOW     BMW
                      AUDI
                      VOLKSWAGEN

SQL> select a.code, a.colour, b.car from robla.test1 a, robla.test2 b where a.code = b.code(+) order by a.code;

      CODE COLOUR     CAR
---------- ---------- ------------------------------
         1 GREEN      VOLVO
         2 BLUE
         3 YELLOW     BMW

Difference between full outer join and cartesian join

  • A Full outer join will fetch at maximum 'addition of 2 tables' where as the Cartesian product will fetch the 'product of 2 tables'. Ex: Table A - 2 rows; Table B - 3 rows.
    • A full outer join will fetch in 2+3 = 5 rows.
    • A Full outer join will fetch in 2x3 = 6 rows