SQL Right Outer Join Examples

Updated on 07 Feb 2012,
Published on 28 Feb 2008

In the previous article regarding sql left outer join we learnt left outer join that retrieves all the results from left table and related matches from the right table where right table having no matches displays the Null value in the corresponding columns. Consider the same example of authors and publishers table of the existing database PUBS of sql server 2000. 

We used the following left outer join query:

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

Just change the left keyword to right outer join in above example; you will get the reverse output of left outer join in the form of right outer join. 

SQL Right Outer Join query Example

SELECT				A.AU_FNAME, 
					A.AU_LNAME, 
					P.PUB_NAME
FROM				AUTHORS A 
RIGHT 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

New Moon Books

NULL

NULL

Binnet & Hardley

NULL

NULL

Five Lakes Publishing

NULL

NULL

Ramona Publishers

NULL

NULL

GGG&G

NULL

NULL

Scootney Books

NULL

NULL

Lucerne Publishing

Abraham

Bennet

Algodata Infosystems

Cheryl

Carson

Algodata Infosystems

Notice the difference in the output of right outer join and left outer join. Right outer join returned all the rows from right table as all publisher names and null values for the left table columns having no match found in left table’s au_fname and au_lname.

Continue to next tutorial: SQL Full Outer Join Examples to learn the implementation of full outer join for joining the results of multiple sql tables.

0 Responses to "SQL Right Outer Join Examples"
Leave a Comment
* required
* required
* will not be published
* optional
* hint: http://www.example.com
  • Subscribe via Email