JOINS IN MySQL

Join:

  • Join is use to get information to multiple table base on condition.
  • According to our requirement just splitting of information  into multiple table using joins we can combine those table to get data

Joins are divided in 3 type:

  1. Inner Join.
  2. Outer Join.
  3. Cross or partition Join.

Inner Join:

1.Equi Join:

  • If we are getting records from multiple table based on equi condition comes under equi join.
  • In the equi-join the comparison we are making between two columns is that they match the same value.
  • We can use this method to select certain fields from both tables and only the correct rows will be joined together.
Syntax

SELECT column_name
FROM Tablename1, Tablename2
WHERE (Table1.column = Table2.column)

Example

SELECT emp.ename,dept.dname
FROM emp,dept
WHERE (emp.deptno = dept.deptno);

Example
SELECT e.ename,d.dname,cid.cname
FROM emp e,dept d,city c
WHERE (e.deptno = d.deptno and e.cid=c.cid);

2.Non-equi join:

  • Using this we can get records from multiple table without equal condition.
Example
SELECT e.ename,s.grade
FROM emp e, salgrade s
WHERE e.sal between s.lowsal and s.highsal

 

SELECT e.ename,s.grade
FROM emp e join salgrade
on e.sal between s.lowsal and s.highsal

Outer Join :

  1. By using outer join we can get matching and un-matching records from two table.
  2. Outer joins are divided into 3 type:
  •  Left Outer Join.
  • Right outer Join.
  • Full Outer Join. 

1.Left Outer Join:

  • we can get matching and un-matching record from left side table only matching record  from RHS table.
Syntax

SELECT columnsname
FROM tablename
LEFT [OUTER] JOIN table2
ON tablename.columnname = tablename.columnname;

Example

select emp.ename ,dept.dname
from emp left outer join dept
on emp.deptno = dept.deptno;

2.Right Outer Join:

  • we can get matching and un-matching record from right side table only matching record  from LHS table.
Syntax

SELECT columnsname
FROM tablename
RIGHT [OUTER] JOIN table2
ON tablename.columnname = tablename.columnname;

Example

select emp.ename ,dept.dname
from emp right outer join dept
on emp.deptno = dept.deptno;

3.Full Outer Join:

  • It is combination of left and right outer joins use to get the matching and un-matching  records from both the tables.
  • Using UNION key word we can join this two table.
Syntax
SELECT columnsname
FROM tablename
LEFT [OUTER] JOIN table2
ON tablename.columnname = tablename.columnname;
union
SELECT columnsname
FROM tablename
Right [OUTER] JOIN table2
ON tablename.columnname = tablename.columnname;
Example
select emp.ename ,dept.dname
from emp left outer join dept
on emp.deptno = dept.deptno;
union
select emp.ename ,dept.dname
from emp right outer join dept
on emp.deptno = dept.deptno;

 

 

 

 

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *