java2db

Sql right outer join on tables with an example



The  right outer join returns all rows of the right table even though there are no matches with the left table.

 
right outer joinIf any match exist in the left table based on the join-predicate (tab1.col_name = tab2.col_name),it

……will return columns from the both tables(left and right table) as a result rows.
right outer join If any match does not exist in the left table based on the join-predicate

……(tab1.col_name = tab2.col_name),it will return all rows of the  right table columns with data and

……left table columns with “null“.

 

Syntax:

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

 

 

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

Example table able 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

 

Example Query for right outer join:

The PLANET_ID = 7  of the table moon is not there in the table planet . If we join the two tables with right join , the result table columns filled with null .

PLANET_NAME
PLANET_COLOR
MOON_NAME
Earth Blue Gray
Jupiter Multi Unknown
Mars Red Silver
Saturn Yellow Orange
null null Charon (pluto moon)

 

 

Note : We can get the same result  with the below SQL  statement(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 ;



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 full 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]



^ <