MySQL - Right Join

Understanding Outer Joins: The MySQL RIGHT JOIN

In SQL, joins are used to combine rows from two or more tables based on a related column. While an INNER JOIN only returns rows where the join condition is met in both tables, an OUTER JOIN (LEFT, RIGHT, or FULL) also returns rows that do not have a match in the other table.

What is a RIGHT JOIN?

A RIGHT JOIN (also known as RIGHT OUTER JOIN) returns all rows from the right table and the matched rows from the left table. If a row in the right table has no match in the left table, the result will still include that row, but with NULL values for all columns from the left table.

Think of it as: “Give me everything from the right table, and if you find any matching information in the left table, bring that along too.”

Syntax

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

Best Practice: Many developers find LEFT JOIN more intuitive. Any RIGHT JOIN can be rewritten as a LEFT JOIN by simply swapping the order of the tables. For example, A RIGHT JOIN B is equivalent to B LEFT JOIN A.

Practical RIGHT JOIN Example

Let’s consider a scenario with two tables: employees and departments. We want to list all departments, and if a department has an employee assigned to it, we want to show that employee’s name.

Example Tables

First, the employees table:

CREATE TABLE employees (
    id INT PRIMARY KEY, name VARCHAR(50), dept_id INT
);
INSERT INTO employees VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 101);

Next, the departments table. Note that ‘Marketing’ (ID 103) has no employees assigned yet.

CREATE TABLE departments (
    id INT PRIMARY KEY, name VARCHAR(50)
);
INSERT INTO departments VALUES
(101, 'Sales'),
(102, 'Engineering'),
(103, 'Marketing');

The RIGHT JOIN Query

We’ll use a RIGHT JOIN to ensure all departments are listed, regardless of whether they have employees.

SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    employees AS e
RIGHT JOIN
    departments AS d ON e.dept_id = d.id;

Output

The result correctly shows all departments. For ‘Marketing’, which has no employees, employee_name is NULL.

employee_name	department_name
Alice	Sales
Charlie	Sales
Bob	Engineering
NULL	Marketing

RIGHT JOIN with WHERE Clause

You can use a WHERE clause to filter the results of a join. A common use case with outer joins is to find rows that exist in one table but not the other.

Example: Finding Unstaffed Departments

By filtering for rows where the left side is NULL, we can find all departments that have no employees.

SELECT d.name AS department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.dept_id = d.id
WHERE e.id IS NULL;

Output

department_name
Marketing

RIGHT JOIN in a Client Program

Executing joins in client programs is a common task. As always, use prepared statements to prevent SQL injection, even for SELECT queries.

Example (Python with mysql-connector-python)

Python
NodeJS
Java
PHP



import mysql.connector

def get_department_roster():
    db_config = {'user': 'root', 'password': 'password', 'database': 'your_db'}
    query = """
        SELECT e.name, d.name
        FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id
        ORDER BY d.name, e.name
    """
    with mysql.connector.connect(**db_config) as conn:
        with conn.cursor(dictionary=True) as cursor: # dictionary=True is very useful
            cursor.execute(query)
            results = cursor.fetchall()
            for row in results:
                employee = row['name'] if row['name'] else 'N/A'
                print(f"Department: {row['d.name']}, Employee: {employee}")

get_department_roster()



const mysql = require('mysql2/promise');

async function getDepartmentRoster() {
    const conn = await mysql.createConnection({ /* config */ });
    const query = `
        SELECT e.name AS employeeName, d.name AS departmentName
        FROM employees AS e
        RIGHT JOIN departments AS d ON e.dept_id = d.id
    `;
    try {
        const [rows] = await conn.query(query);
        console.log(rows);
    } finally {
        await conn.end();
    }
}
getDepartmentRoster();



public void printDepartmentRoster() {
    String sql = "SELECT e.name, d.name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id";
    try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {

        while (rs.next()) {
            String employeeName = rs.getString("e.name");
            String departmentName = rs.getString("d.name");
            System.out.printf("Employee: %s, Department: %s%n",
                employeeName == null ? "(No employee)" : employeeName, departmentName);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}



$pdo = new PDO(/* DSN */);
$sql = 'SELECT e.name AS employeeName, d.name AS departmentName
        FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id';

$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $employee = $row['employeeName'] ?? '(No employee)';
    echo "Department: {$row['departmentName']}, Employee: {$employee}\n";
}