Skip to main content

JOIN Operation in SQL

JOIN:
            Join is used to combine records from one or more table in database. Join operation take place on related record in tables with some specific  condition. 
JOIN types:
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
Example:
Consider the following table to join.
Employee
 id     Emp_name      Age         Salary Dept_id Location
 1 Jebas  35 55000 2 TVL
 2             Thomas  30 70000 1 TVL
 3 Mark              25 55000 2 TEN
 4         Harry  42 55000 2 TUTY
 5 Willam  45 70000 1 TUTY
 6 James  32 45000 3 TEN
 7 Mock  22 45000 3
 KOVI
 8 Sherin  23 12000 7 TUTY
 9 Don Meon  41 78000 NULL MDY
 10 Moses  26 23000 4 RAMD
Dept_table
 Dept_id Dept_name
     1 Director
     2 Manager
     3 Employee
     4 HR
     5 TeamLeader
     6 Developer
     7 Tester
     8 Supporter  
INNER JOIN:
                    The inner joins will return records that are common to both table. 
SELECT Employee.Emp_name ,dept_table.dept_name from Employee
  INNER JOIN dept_table ON Employee .dept_id = dept_table .dept_id ;
This statement return department name of each employee.
 Emp_name     Dept_name
 Jebas Manager
 Thomas Director
 Mark             Manager
 Harry Manager
 Willam Director
 James Employee
 Mock Employee
 Sherin Tester
 Moses HR

RIGHT JOIN or RIGHT OUTER JOIN:
                    The RIGHT JOIN return records that are common  and also return records that are not matches in  left  side 
SELECT Employee.Emp_name ,dept_table.dept_name from dept_table
 RIGHT JOIN Employee ON dept_table .dept_id=Employee .dept_id ;
 Emp_name     Dept_name
 Jebas Manager
 Thomas Director
 Mark             Manager
 Harry Manager
 Willam Director
 James Employee
 Mock Employee
 Don Meon NULL
 Sherin Tester
 Moses HR

LEFT JOIN or LEFT OUTER JOIN
                       The LEFT JOIN return records that are common and also return records that are not matches in right side.
SELECT Employee.Emp_name ,dept_table.dept_name from dept_table
 RIGHT JOIN Employee ON  dept_table .dept_id =Employee .dept_id ;
 Emp_name     Dept_name
 Jebas Manager
 Thomas Director
 Mark             Manager
 Harry Manager
 Willam Director
 James Employee
 Mock Employee
 Moses HR
 NULL TeamLeader
 NULL Developer
 Sherin Tester
 NULL Supporter

FULL JOIN or FULL OUTER JOIN:
                        The FULL JOIN return records that are common and also unmatched records.
SELECT Employee.Emp_name ,dept_table.dept_name from Employee
 FULL JOIN dept_table ON Employee .dept_id = dept_table .dept_id ;
 Emp_name     Dept_name
 Jebas Manager
 Thomas Director
 Mark             Manager
 Harry Manager
 Willam Director
 James Employee
 Mock Employee
 Don Meon NULL
 Moses HR
 NULL TeamLeader
 NULL Developer
 Sherin Tester
 NULL Supporter


Thank You. Like, Share and Subscribe 👍 Followlect.com

Comments