MySQL

In MySQL, the RIGHT JOIN is used to return all records from the right (or second) table and the matching records from the left (or first) table. If there is no match, NULL values are returned for columns from the left table. This join type is useful when you want to retrieve all the data from the right table, even if some rows do not have corresponding data in the left table.

Here’s a detailed guide on how to use the RIGHT JOIN in MySQL, with examples for new students:

1. Basic Syntax of RIGHT JOIN:

   • The RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there is no match, NULL is returned for columns from the left table.
   • Syntax:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id;

   • This query retrieves all department names from the departments table, along with the first name and last name of employees who belong to those departments. If a department has no employees, NULL will be returned for the employee columns.

2. Using RIGHT JOIN with WHERE Clause:

   • You can filter the results of a RIGHT JOIN using a WHERE clause.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE d.department_name = 'Sales';

   • This query returns all employees in the Sales department and the department name, including departments with no employees.

3. Handling NULL Values with RIGHT JOIN:

   • Since RIGHT JOIN includes rows from the right table even if there is no match in the left table, you can use IS NULL to identify rows where there is no matching record in the left table.
   • Example:

   SELECT d.department_name, e.first_name, e.last_name 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE e.employee_id IS NULL;

   • This query retrieves departments that do not have any employees.

4. Using RIGHT JOIN with Multiple Tables:

   • You can perform RIGHT JOIN with more than two tables to retrieve data from multiple related tables.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name, b.branch_name 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id 
   RIGHT JOIN branches b 
   ON d.branch_id = b.branch_id;

   • This query retrieves employees, department names, and branch names. It returns all branches, even if there are no matching departments or employees.

5. Using RIGHT JOIN with Aggregate Functions:

   • You can combine RIGHT JOIN with aggregate functions like COUNT(), SUM(), and AVG() to analyze data across related tables.
   • Example:

   SELECT d.department_name, COUNT(e.employee_id) AS employee_count 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id 
   GROUP BY d.department_name;

   • This query returns all departments and the number of employees in each. Departments with no employees will still be listed with a count of 0.

6. Combining RIGHT JOIN with ORDER BY:

   • You can use RIGHT JOIN with ORDER BY to sort the results based on columns from either the left or right table.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id 
   ORDER BY d.department_name ASC;

   • This query returns all departments and employees, sorted by department name in ascending order.

7. Performance Considerations:

   • Using RIGHT JOIN on large datasets can impact query performance. Indexing the columns used in the ON clause can improve performance.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE e.salary > 60000;

   • Indexing department_id in both tables can make this query run more efficiently.

8. Combining RIGHT JOIN with Other Joins:

   • You can combine RIGHT JOIN with other types of joins like LEFT JOIN and INNER JOIN to retrieve complex datasets from multiple tables.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name, b.branch_name 
   FROM employees e 
   RIGHT JOIN departments d 
   ON e.department_id = d.department_id 
   LEFT JOIN branches b 
   ON d.branch_id = b.branch_id;

   • This query retrieves all departments and branches, and employees if they exist, ensuring that all departments and branches are included in the result.

The RIGHT JOIN is useful when you need to retrieve all records from one table (the right table) even if there are no matching records in the related table (the left table). This makes it a great tool when working with datasets where the right table must be fully included in the results.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT 
    prd_product.product_id, 
    prd_product.product_code, 
    prd_product.product_name,
    prd_product.selling_price, 
    prd_product.purchase_price,
    prd_product_type.product_type_id, 
    prd_product_type.product_type
FROM prd_product
RIGHT OUTER JOIN prd_product_type ON prd_product_type.product_type_id = prd_product.product_type_id;

EXAMPLE 1 - SQL RIGHT JOIN

Here is a clear example of a RIGHT JOIN. Retrieve information of all products along with their respective product type, including product types that have not been associated with any products.

EXAMPLE 1 - Tansy Academy Data Model

In this task, you will create a query involving two tables, named Products and ProductType, marked are the columns necessary for the query.

EXAMPLE 1 - RIGHT JOIN query

To achieve this, you need to execute a RIGHT JOIN between the product table and the product type detail table, utilizing the primary key and foreign key column, wherein the product type ID column serves as the joining column. In this scenario, the product table functions as the left table, and the product type detail table is considered the right table. The left table, which contains the essential primary business information, focuses on the product list as the primary requirement. At a secondary level, we seek prodcut type name, so we treat the product type as the secondary table, positioned on the right side of the join

SELECT prd_product.product_id, prd_product.product_code, prd_product.product_name, prd_product.selling_price, prd_product.purchase_price, prd_product_type.product_type_id, prd_product_type.product_type FROM prd_product RIGHT OUTER JOIN prd_product_type ON prd_product_type.product_type_id = prd_product.product_type_id ORDER BY prd_product.product_id;

EXAMPLE 1 - Query Data Mapping

In this visual representation, a yellow background denotes a correspondence between the primary key and foreign key. Meanwhile, a light yellow background with red font on the right side signifies products lacking a corresponding row in the product type table. These product typess will be included in the final result, but with NULL values for product details. It's important to recognize that a RIGHT JOIN incorporates all rows from the right table, which, in this case, is the product type table.

EXAMPLE 1 - Final OUTput

Please note that for rows in the left table that do not find a corresponding match in the right table, the values are marked as null.