SQL Full Outer Join Examples

by top54u.com 28 Feb, 2008

Inside SQL Server 2005 | SQL Tutorials

Types of Joins in Sql Server 2005

SQL Right Outer Join Examples

Joins using table aliases in SQL Server 2005

Self Join In Sql Server 2000 2005

SQL Inner Join Examples

SQL Left Outer Join Examples

Joins In Sql Server 2005

To retrieve all the records from left as well as right table unless the records have matching relations in each row you can use SQL FULL OUTER JOIN.

You can consider the examples of last two articles about left outer join and right outer join, in which left outer join retrieves all records from the left table and as all records of right table in right outer join along with null values for the columns having no matching records in any tuple. To retain all the records of left as well as right table along with null values for non matching rows displaying the combination of results of left outer and right outer join, FULL OUTER JOIN is the best solution.

SQL FULL outer join example:

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A FULL OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER BY A.AU_LNAME, A.AU_FNAME

Result:

au_fname	au_lname	pub_name
NULL	NULL	Binnet & Hardley
NULL	NULL	Five Lakes Publishing
NULL	NULL	New Moon Books
NULL	NULL	Lucerne Publishing
NULL	NULL	Scootney Books
NULL	NULL	Ramona Publishers
NULL	NULL	GGG&G
Abraham	Bennet	Algodata Infosystems
Reginald	Blotchet-Halls	NULL
Cheryl	Carson	Algodata Infosystems
Michel	DeFrance	NULL
Innes	del Castillo	NULL
Ann	Dull	NULL
Marjorie	Green	NULL
Morningstar	Greene	NULL
Burt	Gringlesby	NULL
Sheryl	Hunter	NULL
Livia	Karsen	NULL
Charlene	Locksley	NULL
Stearns	MacFeather	NULL
Heather	McBadden	NULL
Michael	O'Leary	NULL
Sylvia	Panteley	NULL
Albert	Ringer	NULL
Anne	Ringer	NULL
Meander	Smith	NULL
Dean	Straight	NULL
Dirk	Stringer	NULL
Johnson	White	NULL
Akiko	Yokomoto	NULL