Nov
13
2008

SQL Joins and Applies – Understanding OUTER JOIN

This is part of a four part post series on SQL Joins and Applies.
If you want to follow the examples given, you can get the T-SQL to create the tables, function and test data from my earlier post SQL Joins and Applies – Test Data.

The other posts in this series are:
SQL Joins and Applies – Understanding INNER JOIN
SQL Joins and Applies – Understanding CROSS JOIN
SQL Joins and Applies – Understanding APPLY

Outer Joins are joins that include rows even if they do not have related rows in the joined table. There are three types of outer join, each one specifies if you will always see the rows from the LEFT, RIGHT or both (FULL) tables.

From Books Online (For an outer Join):

A join that includes rows even if they do not have related rows in the joined table.

LEFT OUTER JOIN
From Books Online:

All rows from the first-named table (the “left” table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear.

SELECT		tsi.sourceValue [sourceValue_left],
		tsi.sysID [sysID_left],
		tss.sysID [sysID_Right],
		tss.sourceValue [sourceValue_right]
FROM		dbo.tblSourceIntegers tsi
LEFT OUTER JOIN	dbo.tblSourceStrings tss
	ON	tsi.sysID = tss.sysID;

Results:

sourceValue_left sysID_left  sysID_Right sourceValue_right
---------------- ----------- ----------- -----------------
100              1           NULL        NULL
200              2           2           Apple
NULL             NULL        NULL        NULL
400              4           NULL        NULL
500              5           5           Dog

(5 row(s) affected)

As above, all of the values in the left table were returned.

RIGHT OUTER JOIN
From Books Online:

All rows in the second-named table (the “right” table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included.

SELECT			tsi.sourceValue [sourceValue_left],
			tsi.sysID [sysID_left],
			tss.sysID [sysID_Right],
			tss.sourceValue [sourceValue_right]
FROM			dbo.tblSourceIntegers tsi
RIGHT OUTER JOIN	dbo.tblSourceStrings tss
	ON		tsi.sysID = tss.sysID;

Results:

sourceValue_left sysID_left  sysID_Right sourceValue_right
---------------- ----------- ----------- -----------------
200              2           2           Apple
NULL             NULL        3           Banana
NULL             NULL        NULL        NULL
500              5           5           Dog
NULL             NULL        6           Egg

(5 row(s) affected)

As above, all of the values in the right table were returned.

FULL OUTER JOIN
From Books Online:

All rows in all joined tables are included, whether they are matched or not.

SELECT		tsi.sourceValue [sourceValue_left],
		tsi.sysID [sysID_left],
		tss.sysID [sysID_Right],
		tss.sourceValue [sourceValue_right]
FROM		dbo.tblSourceIntegers tsi
FULL OUTER JOIN	dbo.tblSourceStrings tss
	ON	tsi.sysID = tss.sysID;

Results:

sourceValue_left sysID_left  sysID_Right sourceValue_right
---------------- ----------- ----------- -----------------
100              1           NULL        NULL
200              2           2           Apple
NULL             NULL        NULL        NULL
400              4           NULL        NULL
500              5           5           Dog
NULL             NULL        3           Banana
NULL             NULL        NULL        NULL
NULL             NULL        6           Egg

(8 row(s) affected)

Looking at the above result, all of the values in the left table were returned first, followed by the unmatched values from the right table.

Written by John in: SQL Tips,Tips |

No Comments »

RSS feed for comments on this post. TrackBack URL

Leave a comment