SQL Left Outer Join Examples

Updated on 07 Feb 2012,
Published on 28 Feb 2008

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.

Continue to next tutorial: SQL Right Outer Join Examples to learn the implementation of right outer join of sql server tables using sql query.

Previous Tutorial

Next Tutorial