PostgreSQL: Understanding and Using RIGHT JOIN

Combine data from two tables using PostgreSQL's RIGHT JOIN. This guide explains the syntax, usage, and how it differs from other join types.

Understanding PostgreSQL's `RIGHT JOIN` Clause

In PostgreSQL, the `RIGHT JOIN` (or `RIGHT OUTER JOIN`) clause combines rows from two tables based on a join condition. Unlike an `INNER JOIN`, which returns only matching rows from both tables, `RIGHT JOIN` returns *all* rows from the right table and the matching rows from the left table. If there's no match on the left, the left side columns in the result will have `NULL` values.

PostgreSQL `RIGHT JOIN` Syntax

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

    

This joins `table1` (left) and `table2` (right). The `ON` clause specifies the join condition. All rows from `table2` are included. Rows from `table1` are included only if they satisfy the join condition; otherwise, `NULL` values appear for columns from `table1`.

Example 1: Joining Two Tables

(Note: This example assumes you have `Employee` and `department` tables with a common `emp_id` column. Screenshots from the original text are not included here. Please refer to the original document for visual verification of the examples. The descriptions below aim to convey the information present in those screenshots.)

SELECT d.emp_id, d.dept_name, d.location, e.emp_fname, e.emp_lname
FROM Employee e
RIGHT JOIN department d
ON e.emp_id = d.emp_id
ORDER BY d.emp_id;

    

How `RIGHT JOIN` Works

Selects all rows from the right table (`department`).
Matches rows from the left table (`Employee`) based on the `ON` condition (`department.emp_id = Employee.emp_id`).
If a row from the right table has a match in the left table, a combined row is created and added to the result.
If a row from the right table has no match on the left, `NULL` values are used for columns from the left table in the result.

Using Table Aliases

Table aliases make `RIGHT JOIN` queries more readable, particularly when tables share column names. Use aliases to give each table a shorter, more descriptive name:

SELECT e.emp_id, e.emp_fname, d.dept_name, d.location
FROM Employee AS e
RIGHT JOIN department AS d
ON e.emp_id = d.emp_id;

    

Using the `USING` Clause

If both tables have a column with the same name, the `USING` clause simplifies the join condition:

SELECT emp_id, emp_fname, emp_lname, dept_name, location
FROM Employee
RIGHT JOIN department
USING (emp_id);

    

`RIGHT JOIN` with `WHERE` Clause

The `WHERE` clause filters results after the join:

SELECT emp_id, emp_fname, emp_lname, dept_name, location
FROM Employee
RIGHT JOIN department
ON Employee.emp_id = department.emp_id
WHERE dept_name = 'Sales';

    

Joining Multiple Tables

You can use `RIGHT JOIN` with multiple tables to combine data from several tables. The order of the joins matters.

SELECT e.emp_id, e.emp_fname, d.dept_name, j.job_description
FROM Employee e
RIGHT JOIN department d ON e.emp_id = d.emp_id
RIGHT JOIN Jobs j ON d.emp_id = j.job_id;

    

Getting Unmatched Records

A `RIGHT JOIN` is particularly useful when you want to retrieve all records from the right table even if there's no match in the left table.

Follow On

TutorialsArena