Searching...
Tuesday, 9 April 2013

SQL Joins Simple examples

18:18

Here are some simple examples for SQL joins.(Inner,Outer,Full). I hope this will help to understand the basics of SQL joins.

Tables Used

Below two tables used to describe the queries (pretechEmployee and pretechDepartment)

clip_image002

clip_image004

INNER JOIN

Inner join is using the fetch the common records from both the tables, here i want to fetch the records which are there in the both employee and department table.

clip_image006

SELECT * FROM pretechEmployee e1 INNER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

clip_image008

 

LEFT OUTER JOIN

As its names says it is using to fetch all records of employee table(left) and fetch the department details of employees

clip_image010

SELECT * FROM pretechEmployee e1 LEFT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

clip_image012

See one scenario, I want the employee details who is not under any department see below query.

clip_image014

SELECT * FROM pretechEmployee e1 LEFT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id WHERE d1.emp_id is null

clip_image016

 

RIGHT OUTER JOIN

Right outer join help us to fetch all records of department (Right) and the employee details who all are having the department.

clip_image018

SELECT * FROM pretechEmployee e1 RIGHT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

clip_image020

clip_image022

SELECT * FROM pretechEmployee e1 RIGHT OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id WHERE e1.emp_id is null

clip_image024

 

FULL OUTER JOIN

Full outer join will fetch all the records from employee and Departments.

clip_image026

SELECT * FROM pretechEmployee e1 FULL OUTER JOIN pretechDepartment d1 ON e1.emp_id=d1.emp_id

clip_image028

Below full outer join query will fetch all the records from employee and department except employees are in department

clip_image030

clip_image032

0 comments: