sql join

Joins

To get output from multiple tables we can use joins.
Using join, we can manipulate on two tables using single query.
Join can be applied if both tables contains same data type and width for column.

There are three types of joins

1. Inner
2. Outer (left, right)
3. Cross

Inner join

It is also called equi join.
It compares two columns from two tables using = operator.

Syntax:
select <columnName1>,<columnName2> from
<tableName1>Inner join<tableName2> on
<tableName1>.<columnName1> = <tableName2.>.<columnName2>;

Example:
select member_master.Name, member_master.Age, employee_master.salary
from member_master
inner join employee_master on
member_master.ID = employee_master.ID;

Outer Join

Outer join can be better illustrated using its types.

1. Left outer join

left outer join will take all the rows from left side table.
If there will be more rows in right side table, it will be truncated and if there will be less rows, null will be added as data.

syntax:
select <columnName1>,<columnName2>
from <tableName1> left join <tableName2> on
<tableName1>.<columnName> = <tableName2>.<columnName>;

2. Right Outer Join

right outer join is like left join but will take all rows from right side table.

Syntax:
select <columnName1>,<columnName2>
from <tableName1>right join <tableName2> on
<tableName1>.<columnName1> = <tableName2>.<columnName2>;

Cross Join

When we apply cross join, it will take first row from left side table and put it with each right side rows.
For example, table1 having 5 rows and table2 having 3rows
then first row from table1 will be placed with 3 rows of table2
and then 2nd row will be placed with 3 rows again and so on.