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.

2 Responses to "SQL Left Outer Join Examples"
Kirubanandan
Good example..... easy to follow ..... thanks
KiranRaj
Really Simple to understand, good......thanks
Leave a Comment
* required
* required
* will not be published
* optional
* hint: http://www.example.com
  • Subscribe via Email