SQL Left Outer Join Examples

by top54u.com on 28 Feb, 2008

Inside SQL Server 2005 | SQL Tutorials

Types of Joins in Sql Server 2005

Self Join In Sql Server 2000 2005

Joins using table aliases in SQL Server 2005

Inner joins return only those rows from both sql database tables having matching records in both the tables whereas left outer join returns all the rows from the left table and related matching records from the other one.

SQL Left Outer Join Example:

USE PUBS

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

Result:

au_fname	au_lname	pub_name
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

This left outer join query retrieves the author names and publisher name having same cities. Here all rows retrieved from the left table i.e. authors and publisher name having the similar city other columns of pub_name column are null due to no match found in the right table.

Learn SQL Inner Joins:

SQL Inner Equi Join Examples

SQL Inner Natural Join Examples

SQL Inner Join Examples

Spotlight.....

Be the first to rate this post