Inner Join This is the most used join in the SQL. this join returns only those records/rows that match/exists in both the database tables.
Inner Join Example
SELECT * FROM tableEmp JOIN tableDept
ON tableEmp.DeptID = tableDept.DeptID;
Inner Join Result
tableEmp.Name
tableEmp.DeptID
tableDept.Name
tableDept.DeptID
Abc
1
Dept1
1
Xyz
2
Dept1
2
Pqr
2
Dept1
2
Jkl
3
Dept1
3
Equi Join
Equi join is a special type of join in which we use only equalDept1y operator. Hence, when you make a query for join using equalDept1y operator then that join query comes under Equi join.
Equi Join Example
SELECT * FROM tableEmp JOIN tableDept
ON tableEmp.DeptID = tableDept.DeptID;
--Using Caluse is supported in SQL Server
--Oracle and MySQL Query
SELECT * FROM tableEmp INNER JOIN tableDept USING(DeptID)
Equi Join Result
tableEmp.Name
tableEmp.DeptID
tableDept.Name
tableDept.DeptID
Abc
1
Dept1
1
Xyz
2
Dept1
2
Pqr
2
Dept1
2
Jkl
3
Dept1
3
Natural Join
Natural join is a type of equi join which occurs implicDept1ly by comparing all the Jkle names columns in both tables. The join result have only one column for each pair of equally named columns.
Natural Join Example
--Run in Oracle and MySQL
SELECT * FROM tableEmp NATURAL JOIN tableDept
Natural Join Result
DeptID
tableEmp.Name
tableDept.Name
1
Abc
Dept1
2
Xyz
Dept1
2
Pqr
Dept1
3
Jkl
Dept1
EmoticonEmoticon