This is just a simple article visually explaining SQL JOINs. In this article I am going to discuss seven different ways you can return data from two relational tables. The seven Joins I will discuss are: Inner JOIN, Left JOIN, Right JOIN, Outer JOIN, Left Excluding JOIN, Right Excluding JOIN, Outer Excluding JOIN, while providing examples of each.
|
INNER JOIN
A_PK A_Value B_Value B_PK
----------- ---------- ---------- -----------
1 FOX TROT 1
2 COP CAR 2
3 TAXI CAB 3
6 WASHINGTON MONUMENT 6
7 DELL PC 7
(5 row(s) affected)
LEFT JOIN
A_PK A_Value B_Value B_PK
----------- ---------- ---------- -----------
1 FOX TROT 1
2 COP CAR 2
3 TAXI CAB 3
4 LINCOLN NULL NULL
5 ARIZONA NULL NULL
6 WASHINGTON MONUMENT 6
7 DELL PC 7
10 LUCENT NULL NULL
(8 row(s) affected)
RIGHT JOIN
A_PK A_Value B_Value B_PK
----------- ---------- ---------- -----------
1 FOX TROT 1
2 COP CAR 2
3 TAXI CAB 3
6 WASHINGTON MONUMENT 6
7 DELL PC 7
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11
(8 row(s) affected)
OUTER JOIN
A_PK A_Value B_Value B_PK
----------- ---------- ---------- -----------
1 FOX TROT 1
2 COP CAR 2
3 TAXI CAB 3
4 LINCOLN NULL NULL
5 ARIZONA NULL NULL
6 WASHINGTON MONUMENT 6
7 DELL PC 7
10 LUCENT NULL NULL
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11
(11 row(s) affected)
LEFT JOIN EXCLUDING INNER JOIN
A_PK A_Value B_Value B_PK
----------- ---------- ---------- -----------
4 LINCOLN NULL NULL
5 ARIZONA NULL NULL
10 LUCENT NULL NULL
(3 row(s) affected)
RIGHT JOIN EXCLUDING INNER JOIN
A_PK A_Value B_Value B_PK
----------- ---------- ---------- -----------
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11
(3 row(s) affected)
OUTER JOIN EXCLUDING INNER JOIN
OR
LEFT & RIGHT JOINS EXCLUDING INNER JOIN
A_PK A_Value B_Value B_PK
----------- ---------- ---------- -----------
4 LINCOLN NULL NULL
5 ARIZONA NULL NULL
10 LUCENT NULL NULL
NULL NULL MICROSOFT 8
NULL NULL APPLE 9
NULL NULL SCOTCH 11
(6 row(s) affected)
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.