SQL - Join - Right

A right join is a type of join operation in SQL that returns all the records from the right table and matching records from the left table. In cases where there are no matches in the left table, the result set will contain NULL values.

A right join is used when you want to include all the records from the right table in the result set, regardless of whether there are matching records in the left table.

Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

In this syntax, table1 is the left table and table2 is the right table. The ON clause is used to specify the join condition.

Example:

Let's consider two tables, orders and customers. The orders table contains information about orders placed by customers, while the customers table contains information about the customers.

Now let's say we want to retrieve all the orders, along with the customer information, including customers who have not placed any orders yet. We can use a right join as follows:

SELECT *
FROM orders
RIGHT JOIN customers ON orders.CustomerID = customers.CustomerID;

Consider two tables: Employees and Departments. The Employees table has the following columns: EmployeeID, Name, DepartmentID, and Salary. The Departments table has the following columns: DepartmentID and DepartmentName.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

This query will return all departments and their corresponding employees, including those departments that have no employees. If there is no match for an employee in the Employees table, NULL values will be returned for the employee's fields.

SELECT Employees.Name, Departments.DepartmentName
FROM Departments
LEFT JOIN Employees
ON Departments.DepartmentID = Employees.DepartmentID
WHERE Employees.Name IS NULL;

This query will return all departments that do not have any employees assigned to them. The LEFT JOIN returns all departments and their corresponding employees, but the WHERE clause filters out the departments that have employees and only returns those with NULL values in the Name column.