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
Like this:
Like Loading...