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
Binnet & Hardley
Five Lakes Publishing
New Moon Books
Lucerne Publishing
Scootney Books
Ramona Publishers
GGG&G
Abraham
Bennet
Algodata Infosystems
Reginald
Blotchet-Halls
Cheryl
Carson
Michel
DeFrance
Innes
del Castillo
Ann
Dull
Marjorie
Green
Morningstar
Greene
Burt
Gringlesby
Sheryl
Hunter
Livia
Karsen
Charlene
Locksley
Stearns
MacFeather
Heather
McBadden
Michael
O'Leary
Sylvia
Panteley
Albert
Ringer
Anne
Meander
Smith
Dean
Straight
Dirk
Stringer
Johnson
White
Akiko
Yokomoto
Above output retrieved from the sql full outer join query is the exact combination of both the left as well as right join outputs.
Also learn other types of SQL Outer Join:
SQL Left Outer Join Examples
SQL Right Outer Join Examples
Be the first to rate this post
Tags: sql, sql joins, sql full outer join, sql outer joins, sql left outer join, sql right outer join, sql join examples
10/11/2008 3:31:12 AM