Understanding SQL RIGHT JOIN with Indian Data

The SQL RIGHT JOIN (also known as RIGHT OUTER JOIN) combines data from two or more tables. Unlike the LEFT JOIN, the RIGHT JOIN retrieves all rows from the right table, and matching rows from the left table. If no match is found, NULL values are returned for the left table’s columns.

What is SQL RIGHT JOIN?

A RIGHT JOIN returns all rows from the right table (in our case, the departments table), and matching rows from the left table (the employees table). If there is no match, NULL values are shown for columns from the left table.

Example: A RIGHT JOIN is useful when you want to find all departments, even those without any employees assigned.

Syntax of RIGHT JOIN

SELECT table1.column_name, table2.column_name, ...
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
  

Example with Indian Data

Let’s perform a RIGHT JOIN on the employees and departments tables. This will retrieve all departments, even those without any employees.

Employees Table

EmployeeID	EmployeeName	DepartmentID	City
1	Rahul Sharma	101	Mumbai
2	Anjali Verma	102	Delhi
3	Karan Gupta	103	Bangalore
4	Priya Singh	NULL	Kolkata

Departments Table

DepartmentID	DepartmentName	HeadOffice
101	IT	Mumbai
102	HR	Delhi
103	Finance	Bangalore
104	Marketing	Chennai

Query: Fetch Department and Employee Information with RIGHT JOIN

We can use the following RIGHT JOIN query to fetch all department details, even those without employees:

SELECT employees.EmployeeID, employees.EmployeeName, employees.City, departments.DepartmentName, departments.HeadOffice
FROM employees
RIGHT JOIN departments
ON employees.DepartmentID = departments.DepartmentID;
  

Output

EmployeeID	EmployeeName	City	DepartmentName	HeadOffice
1	Rahul Sharma	Mumbai	IT	Mumbai
2	Anjali Verma	Delhi	HR	Delhi
3	Karan Gupta	Bangalore	Finance	Bangalore
NULL	NULL	NULL	Marketing	Chennai

Explanation of the Result

The query performed a RIGHT JOIN between the employees and departments tables using the common column DepartmentID. The result includes all departments, even those without any employees. Where an employee is associated with a department, the employee details are shown. Where no employees are associated with a department (like **Marketing**), NULL values are returned for the employee-related columns.

Here’s what the output shows:

• Rahul Sharma is in the IT department, with its head office in Mumbai.
• Anjali Verma is in the HR department, with its head office in Delhi.
• Karan Gupta is in the Finance department, with its head office in Bangalore.
• The Marketing department is shown, but there is no employee, so NULL values are returned for the employee details.

Summary

In conclusion, the SQL RIGHT JOIN returns all rows from the right table, along with matching rows from the left table. If there’s no match, NULL values are returned for columns from the left table. It’s useful for finding all records in the right table, including those without matching records in the left table.