java2db

Sql inner join on tables with an example



The inner join returns exact match rows from the two tables based on the join-predicate. It will check each row of the first table with each row of the second table ,  to find all pairs of rows which satisfy the join-predicate.

 
inner join If any matches found, columns of the both tables combined into result row.

inner join If there is no matches , the result will be empty.

 

Syntax:

SELECT column1,column2,… FROM table_one JOIN table_two ON table_one.column = table_two.column
OR
SELECT column1,column2,… FROM table_one INNER 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 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

 

 

Example Query for inner join:

The result of the above sql statement  is

PLANET_NAME
MOON_NAME
MOON_COLOR
Earth Moon Gray
Jupiter Amalthea Unknown
Mars Phobos Silver
Saturn Titan Orange

 

 

Note : We can  get the same result with the below sql statement.

SELECT p.planet_name,m.moon_name,m.moon_color FROM planet p,moon m WHERE m.planet_id=p.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 left outer join on tables with an example
   [will not be published]



^ <