java2db

Sql left outer join on tables with an example



Left outer join returns all the  rows from the left table even though there are no matches with the right table.
left outer joinIf any matches exist in the right table based on the join-predicate(on tab1.col_name = tab2.col_name),it returns the data from the both tables(left and right table) as a result rows.
left outer joinIf any matches does not exist in the right table based on the join-predicate(on tab1.col_name = tab2.col_name),it will return  data from the left table and  null values for the right table columns.

Syntax:

SELECT column1,cplumn2,.. FROM table_one LEFT JOIN table_two ON table_one.col_name = table_two.col_name;
OR
SELECT column1,cplumn2,.. FROM table_one LEFT OUTER JOIN table_two ON table_one.col_name = table_two.col_name;

 

Example table planet  (left table) :

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 moon (right table):

MOON_ID
PLANET_ID
MOON_NAME
MOON_COLOR
1 4 Titan Orange
2 1 Moon Gray
3 3 Amalthea Unknown
4 2 Phobos Silver

Example Query for left outer join:

The result of the above sql statement is

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

 

Note : We can  get the same result with the below sql statement (Based 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 right outer join on tables with an example
Sql inner join on tables with an example
   [will not be published]



^ <