RIGHT JOIN

Introduction to PostgreSQL RIGHT JOIN

The RIGHT JOIN clause joins a right table with a left table and returns every row from the right table, along with matching rows from the left table. When no match exists in the left table, the left-side columns are populated with NULL.

Basic syntax:

SELECT select_list
FROM table1
RIGHT JOIN table2
  ON table1.column_name = table2.column_name;

You can also use the USING shorthand when both tables share the same column name:

SELECT select_list
FROM table1
RIGHT JOIN table2 USING (column_name);

Basic RIGHT JOIN example

Using the DVD rental sample database, the following query retrieves all films and their inventory records — including films with no inventory:

SELECT
  f.film_id,
  f.title,
  i.inventory_id
FROM inventory i
RIGHT JOIN film f ON f.film_id = i.film_id
ORDER BY f.title;

Because film is on the right side, every film row is returned. Films with no inventory entry show NULL in the inventory_id column.

The same query using USING:

SELECT
  f.film_id,
  f.title,
  i.inventory_id
FROM inventory i
RIGHT JOIN film f USING (film_id)
ORDER BY f.title;

RIGHT JOIN with WHERE to find unmatched rows

Add a WHERE filter on a left-table column to isolate rows that have no match — effectively an anti-join:

SELECT
  f.film_id,
  f.title,
  i.inventory_id
FROM inventory i
RIGHT JOIN film f USING (film_id)
WHERE i.inventory_id IS NULL
ORDER BY f.title;

Sample output:

 film_id |         title          | inventory_id
---------+------------------------+--------------
      14 | Alice Fantasia         |         null
      33 | Apollo Teen            |         null
      36 | Argonauts Town         |         null
      38 | Ark Ridgemont          |         null
      41 | Arsenic Independence   |         null

These are films that exist in the catalog but have no physical copies in inventory.

RIGHT JOIN tips

• Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order. Most teams standardize on LEFT JOIN for consistency.
• Filter on a non-nullable left-table column with IS NULL in the WHERE clause to find right-table rows with no match.
• RIGHT OUTER JOIN is identical to RIGHT JOIN — the OUTER keyword is optional.
• Use table aliases to keep multi-join queries readable, especially when the same table appears more than once.
• Index the join column on both tables to avoid sequential scans on large datasets.