WikiGalaxy

Personalize

SQL RIGHT JOINS

Understanding SQL RIGHT JOIN:

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.


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

Example 1: Basic RIGHT JOIN

Using RIGHT JOIN to get all employees and their departments, including departments with no employees.


      SELECT employees.name, departments.name
      FROM employees
      RIGHT JOIN departments
      ON employees.department_id = departments.id;

Console Output:

John, Sales

NULL, Marketing

Combining RIGHT JOIN with WHERE Clause

Example 2: RIGHT JOIN with Condition

Retrieving all products and their suppliers, but only for suppliers located in 'USA'.


      SELECT products.name, suppliers.name
      FROM products
      RIGHT JOIN suppliers
      ON products.supplier_id = suppliers.id
      WHERE suppliers.country = 'USA';

Console Output:

Laptop, TechCorp

NULL, USA Supplies

RIGHT JOIN with Multiple Tables

Example 3: RIGHT JOIN with Three Tables

Fetching all orders, customers, and their respective sales representatives, including reps not associated with any orders.


      SELECT orders.id, customers.name, sales_reps.name
      FROM orders
      RIGHT JOIN customers
      ON orders.customer_id = customers.id
      RIGHT JOIN sales_reps
      ON customers.rep_id = sales_reps.id;

Console Output:

123, Alice, Bob

NULL, NULL, Charlie

RIGHT JOIN with Aggregate Functions

Example 4: Aggregating Data with RIGHT JOIN

Calculating the total sales per region, including regions with no sales.


      SELECT regions.name, SUM(sales.amount) as total_sales
      FROM sales
      RIGHT JOIN regions
      ON sales.region_id = regions.id
      GROUP BY regions.name;

Console Output:

East, 5000

West, NULL

RIGHT JOIN with Subqueries

Example 5: Using Subqueries with RIGHT JOIN

Listing all departments and the number of employees in each, including departments with no employees.


      SELECT departments.name, 
             (SELECT COUNT(*) FROM employees WHERE employees.department_id = departments.id) as employee_count
      FROM departments
      RIGHT JOIN employees
      ON departments.id = employees.department_id;

Console Output:

HR, 10

IT, NULL

RIGHT JOIN with Self Join

Example 6: Self Join with RIGHT JOIN

Finding employees and their managers, including managers without employees.


      SELECT e1.name as employee, e2.name as manager
      FROM employees e1
      RIGHT JOIN employees e2
      ON e1.manager_id = e2.id;

Console Output:

Alice, Bob

NULL, Charlie

RIGHT JOIN with DISTINCT

Example 7: Using DISTINCT with RIGHT JOIN

Retrieving distinct project names and their client names, including clients with no projects.


      SELECT DISTINCT projects.name, clients.name
      FROM projects
      RIGHT JOIN clients
      ON projects.client_id = clients.id;

Console Output:

Project A, Client X

NULL, Client Y

RIGHT JOIN with ORDER BY

Example 8: RIGHT JOIN and Sorting

Listing all courses and instructors, sorted by instructor name, including instructors with no courses.


      SELECT courses.name, instructors.name
      FROM courses
      RIGHT JOIN instructors
      ON courses.instructor_id = instructors.id
      ORDER BY instructors.name;

Console Output:

Math 101, Dr. Smith

NULL, Prof. Johnson