Structured Query Language (SQL) – Understanding Table JOINS

Structured Query Language (SQL) – Understanding Table JOINS

1)      INNER JOIN

The inner join is given as

SELECT d.Dept_name, e.emp_name

FROM Department  d

INNER JOIN employee e

ON d.dept_id = e.dept_id

The INNER JOIN will return all the rows from the tables DEPARTMENT and EMPLOYEE where there is a match from the Department table to the Employee table. If there are records existing in Department with no match in the Employee table, nothing will be displayed.

Sample output

Dept_name Emp_name
Sales Harish
Sales Arun
HR Keath
Marketing Abhilash

2)      LEFT JOIN

The inner join is given as

SELECT d.Dept_name, e.emp_name

FROM Employee e

LEFT JOIN Department d

ON e.dept_id = d.dept_id

It will return all the rows from the table EMPLOYEE irrespective of the match available from the table DEPARTMENT.

Sample output

Dept_name Emp_name
Sales Harish
Sales Arun
HR Keath
Shiny
Marketing Abhilash
Vasant

You can see that there are two entries “Shiny” and “Vasant” with their department names appearing as blank, since all the records from the left table i.e. Employee will be displayed. The list will hence display all those employees who have been assigned a department + all those who have not been assigned a department.

3)      RIGHT JOIN

The inner join is given as

SELECT d.Dept_name, e.emp_name

FROM Employee e

RIGHT JOIN Department d

ON e.dept_id = d.dept_id

It will return all the rows from the table DEPARTMENT irrespective of the match available from the table EMPLOYEE.

Sample output

Dept_name Emp_name
Sales Harish
Sales Arun
HR Keath
Marketing Abhilash
Research

You will find the entry “Research” in the Dept_name column. It indicates there are no employees assigned to this department.

Advertisements

One thought on “Structured Query Language (SQL) – Understanding Table JOINS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s