java2db

Sql full outer join on tables with an example



The  full outer join returns all rows from right and left table.Fill in NULLs for missing matches on either side.So the full join  return the combined results of both the left and right outer joins.

 

 

Syntax:

SELECT column1,column2,..  FROM table_one FULL JOIN table_two ON table_one.column = table_two.column;
(OR)
SELECT column1,column2,..  FROM table_one FULL OUTER JOIN table_two ON table_one.column = table_two.column;

 

 

full outer join Example table planet:

PLANET_ID
PLANET_NAME
PLANET_COLOR
NUMBER_OF_MOONS
1 Earth Blue 1
2 Mars Red 2
3 Jupiter Multi 50
4 Saturn Yellow 53
5 Uranus Blue-green 27
6 Neptune Light-blues 13

full outer joinExample table moon:

MOON_ID
PLANET_ID
MOON_NAME
MOON_COLOR
1 4 Titan Orange
2 1 Moon Gray
3 3 Amalthea Unknown
4 2 Phobos Silver
5 7 Charon (pluto moon) Gray

 

 

Full outer join example Query :

The result of the above sql statement .

PLANET_NAME
PLANET_COLOR
MOON_NAME
Earth Blue Moon
Jupiter Multi Amalthea
Mars Red Phobos
Neptune Light-blue null
Saturn Yellow Titan
Uranus Blue-green null
null null Charon(pluto moon)

 

 

Note : Union of left and right join will give the same result  (Based on old syntax) .

SELECT  p.planet_name,p.planet_color,m.moon_name FROM planet p ,moon m WHERE p.planet_id=m.planet_id(+)
UNION
SELECT  p.planet_name,p.planet_color,m.moon_name FROM planet p , moon m WHERE p.planet_id(+)=m.planet_id;



Related Posts :
Sql FOREIGN KEY constraint on table column
Sql CHECK constraint on table column
Sql NOT NULL constraint on table column
Sql UNIQUE constraint on table column
Sql PRIMARY KEY constraint on table column
Sql right outer join on tables with an example
Sql left outer join on tables with an example
Sql inner join on tables with an example
   [will not be published]



^ <