sql_join_right

SQL RIGHT JOIN Clause

The SQL RIGHT JOIN Clause

The RIGHT JOIN clause (also written as RIGHT OUTER JOIN) is similar to LEFT JOIN. It combines rows from two tables based on a related column. However, RIGHT JOIN returns all rows from the right table (table2) and the matched rows from the left table (table1).

If there is no match for a row in the right table, the result will still include that row, but with NULL values in the columns from the left table.

SQL RIGHT JOIN Syntax

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

The OUTER keyword in RIGHT OUTER JOIN is optional in most SQL database systems and is often omitted.

Conceptually, a RIGHT JOIN works as follows:

1. It starts with all rows from the table2 (the ‘right’ table specified after RIGHT JOIN).

2. For each row from table2, it tries to find matching rows in table1 (the ‘left’ table) based on the condition in the ON clause.

3. If matching rows are found in table1, their columns are combined with the table2 row.

4. If no matching rows are found in table1 for a table2 row, that table2 row is still included, but columns selected from table1 will have NULL values.

Note: Any RIGHT JOIN can be rewritten as a LEFT JOIN by simply swapping the order of the tables. For example, table1 RIGHT JOIN table2 is equivalent to table2 LEFT JOIN table1. Many developers prefer using LEFT JOIN consistently for readability.

Demo Database Context

For this example, let’s use two tables:

• Orders: Contains order information, with columns OrderID and EmployeeID (Foreign Key referencing Employees).

• Employees: Contains employee information, with columns EmployeeID (Primary Key), FirstName, and LastName.

We want to list all employees and any orders they might have processed.

SQL RIGHT JOIN Example

The following SQL statement will select all employees and list their processed orders. If an employee has processed no orders, they will still be listed, but their order-related fields will be NULL.

SELECT O.OrderID, E.FirstName, E.LastName
FROM Orders O
RIGHT JOIN Employees E ON O.EmployeeID = E.EmployeeID
ORDER BY E.LastName, E.FirstName, O.OrderID;

Key points about the result:

• All employees from the Employees table will be included.

• If an employee has handled multiple orders, they will appear multiple times.

• If an employee has handled no orders, their FirstName and LastName will be listed, but O.OrderID will be NULL.

The same result using LEFT JOIN:

SELECT O.OrderID, E.FirstName, E.LastName
FROM Employees E
LEFT JOIN Orders O ON E.EmployeeID = O.EmployeeID
ORDER BY E.LastName, E.FirstName, O.OrderID;

This LEFT JOIN version is often preferred for consistency in query structure.

Practical Use Case:

RIGHT JOIN is useful when you want all records from the ‘right-side’ table and any matching records from the ‘left-side’ table. For example, listing all product categories and any products within them, ensuring all categories appear even if they currently have no products.

To find employees who have not processed any orders using RIGHT JOIN:

SELECT E.FirstName, E.LastName
FROM Orders O
RIGHT JOIN Employees E ON O.EmployeeID = E.EmployeeID
WHERE O.OrderID IS NULL;