Tag Archives for " outer join "

SQL JOINS

This article looks at SQL JOINS including the old skool (+) operator which denotes the “optional” table. The article concludes with FULL JOINS

primarytable
 id   PRNAME
---- ----------
1     primary
2     primary
3     primary
4     primary
optionaltable
 id   OPTNAME
---- ----------
1     optional
3     optional
5     optional

LEFT OUTER JOIN

LEFT OUTER JOINS return all the records from the primary table, plus matching records from the optionaltable or NULL

[sourcecode language=”sql”] SELECT * FROM primarytable pt
LEFT OUTER JOIN optionaltable ot
ON pt.id = ot.id
[/sourcecode]

ID  PRNAME  ID  OPTNAME
1   primary   1  optional
2   primary  
3   primary   3  optional
4   primary  

[sourcecode language=”sql”] SELECT *
FROM primarytable pt, optionaltable ot
WHERE pt.id = ot.id(+)
[/sourcecode]

id   prname  id   optname
1    primary  1    optional
2    primary  
3    primary  3  optional
4    primary  

RIGHT OUTER JOIN

[sourcecode language=”sql”] SELECT *
FROM primarytable pt, optionaltable ot
WHERE pt.ID(+) = ot.ID
[/sourcecode]

id   prname  id  optname
1    primary  1  optional
3    primary  3  optional
              5  optional

[sourcecode language=”sql”] SELECT * FROM primarytable pt
RIGHT OUTER JOIN optionaltable ot
ON pt.id = ot.id
[/sourcecode]

id   prname  id   optname
1    primary  1  optional
3    primary  3  optional
              5  optional
  
RIGHT OUTER JOIN returns all the records from the optionaltable, and matching records from the primarytable or NULL

Other JOINS

FULL OUTER JOIN
A FULL OUTER JOIN returns all the data from both tables

[sourcecode language=”sql”] SELECT * FROM primarytable pt
FULL OUTER JOIN optionaltable ot
ON pt.ID = ot.ID
ORDER BY pt.id
[/sourcecode]

id   prname  id   optname
1  primary     1  optional
2  primary   
3  primary     3  optional
4  primary  
                 5   optional

   
FULL OUTER JOIN – DATA NOT IN COMMON
The following query will return all the data not in common –

[sourcecode language=”sql”] SELECT * FROM primarytable pt
FULL OUTER JOIN optionaltable ot
ON pt.ID = ot.ID
WHERE pt.ID IS NULL
OR ot.id IS null
ORDER BY pt.ID
[/sourcecode]

id   prname  id   optname
2    primary  
4    primary  
             5  optional

Finally, finally

Finally this work of ascii art from http://www.reddit.com/user/wolflarsen over at reddit –

<pre><code>                  TABLE A      TABLE B
               [ ------- ]   [ -- X -- ] --\ -------\
               [         ]   [    X    ]    |        |
               [         ]   [    X    ]  right Outer|
               [         ]   [    X    ]    |        |
     /---- /---[ -- X -- ]   [ -- X -- ]    |     FULL OUTER
    |   Inner  [    X    ]   [    X    ]    |        |
    |      \---[ -- X -- ]   [ -- X -- ]---/         |
 Left Outer    [    X    ]   [         ]             |
    |          [    X    ]   [         ]             |
    |          [    X    ]   [         ]             |
     \---------[ -- X -- ]   [ ------- ]------------/
</code></pre>

Or in a more artistic form – http://imgur.com/ijGaAH5

 Try it yourself

[sourcecode language=”sql”] —
DROP TABLE primarytable;
—
CREATE TABLE primarytable
(
     ID         NUMBER(38),
     PTNAME     VARCHAR2(256)
);
—
INSERT INTO primarytable(id, ptname) values ( 1, ‘primary’);
INSERT INTO primarytable(id, ptname) values ( 2, ‘primary’);
INSERT INTO primarytable(id, ptname) values ( 3, ‘primary’);
INSERT INTO primarytable(id, ptname) values ( 4, ‘primary’);
—
DROP TABLE optionaltable;
—
CREATE TABLE optionaltable
(
     ID                NUMBER(38),
     OPTNAME  VARCHAR2(256)
);
—
INSERT INTO optionaltable(id, optname) values ( 1, ‘optional’);
INSERT INTO optionaltable(id, optname) values ( 3, ‘optional’);
INSERT INTO optionaltable(id, optname) values ( 5, ‘optional’);
[/sourcecode]

References

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

http://www.reddit.com/r/programming/comments/1xlqeu/sql_joins_explained_xpost_rsql/cfczgw3