In
this tutorial we will learn what is join
, Join types in MySql, Join syntax
in MySql,Join query in mysql ,Join tables in MySql.
What is join
Join is a query which can fetch or retrieve
data from more than two tables. Join in
MySql is very important task for a
dba where we need details data from more than two tables. Then we can us join
operation to fetch details data as per our requirement.
Join types in MySql
There are four types of join in MySql.
1.inner
Join: Suppose we have
two tables. Using the inner join we
can retrieve data from these two tables which data are matched .
2.Left
Join: Suppose we have
two tables .Using Left join we can retrieve
all data from the left table and matched data from the right table.
3.Right
Join: Suppose we have
two tables .Using Right join we can retrieve all data from the
right table and matched data from the left table.
4.Cross
Join: Suppose we have
two tables ,one is employee table and other is department table. Department table
has five records and employee table has three records. Using cross join Each row of the one table is
join to every rows of the other table. After joining two tables we will get 15
records. Normally cross join is not used.
Join syntax in
MySql
1)
In
case of inner join we can use the
below syntax.
select * from <first table> inner join <second
table> on first table.column name=second table.column name ;
2)
In
case of left join we can use the
below syntax.
select * from <first table> left join <second table> on first table.column name=second table.column
name ;
3)
In
case of right join we can use the
below syntax.
select * from <first table> right join <second table> on first table.column name=second table.column
name ;
4)
In
case of cross join we can use the
below syntax.
select * from <first table> cross join <second table> ;
Join tables in
MySql
Suppose we have two tables , ,one is
employee table and other is department table. Department table has five records
and employee table has three records. We are going to join two tables.
1)
Incase
of inner join query MySql we can
execute the below join query to retrieve data from two tables.
select * from dept inner join emp on
dept.deptno=emp.empdept ;
2)
Incase
of Left join query in MySql we can execute
the below join query to retrieve data from two tables.
select * from dept
left join emp on dept.deptno=emp.empdept ;
3)
Incase
of Right join query in
MySql we can execute the below join query to retrieve data from two tables.
select * from dept right join
emp on dept.deptno=emp.empdept ;
4)
Incase
of cross join query in
MySql we can execute the below join query to retrieve data from two tables.
select * from dept cross join emp ;
In
this tutorial we have learned what is
join , Join types in MySql, Join syntax
in MySql, Join tables in MySql.
0 comments:
Post a Comment