PostgreSQL RIGHT JOIN

RIGHT JOIN (equivalently RIGHT OUTER JOIN) returns every row from the right table and the matching rows from the left table. Where no match exists in the left table, the left-side columns are NULL. It is the mirror image of LEFT JOIN, and any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table positions.

Syntax

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

Using the USING shorthand when the join column shares the same name:

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

RIGHT OUTER JOIN and RIGHT JOIN are identical — the OUTER keyword is optional.

Practical Example

The schema below represents purchase orders and their assigned account managers. Some purchase orders may not yet have an account manager assigned.

CREATE TABLE account_managers (
  manager_id   SERIAL PRIMARY KEY,
  manager_name VARCHAR(100) NOT NULL,
  region       VARCHAR(60)
);

CREATE TABLE purchase_orders (
  po_id        SERIAL PRIMARY KEY,
  po_number    VARCHAR(20) UNIQUE NOT NULL,
  total_amount NUMERIC(12, 2) NOT NULL,
  manager_id   INT REFERENCES account_managers(manager_id),
  submitted_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO account_managers (manager_name, region) VALUES
  ('Sofia Hernandez', 'North America'),
  ('Dmitri Volkov',   'Europe'),
  ('Aiko Tanaka',     'Asia Pacific'),
  ('Chidi Okeke',     'Africa');         -- no POs assigned yet

INSERT INTO purchase_orders (po_number, total_amount, manager_id) VALUES
  ('PO-2026-001', 15000.00, 1),
  ('PO-2026-002',  3200.00, 2),
  ('PO-2026-003', 87000.00, 1),
  ('PO-2026-004',  4500.00, NULL);      -- unassigned PO

All account managers with their purchase orders

The right table here is account_managers, so every manager appears regardless of whether they have a PO:

SELECT
  am.manager_name,
  am.region,
  po.po_number,
  po.total_amount
FROM purchase_orders po
RIGHT JOIN account_managers am ON am.manager_id = po.manager_id
ORDER BY am.manager_name, po.submitted_at;

Chidi Okeke (no POs) appears in the result with NULL in po_number and total_amount. The unassigned PO (PO-2026-004) does not appear because it has no manager_id.

Anti-join — managers with no purchase orders

SELECT am.manager_name, am.region
FROM purchase_orders po
RIGHT JOIN account_managers am ON am.manager_id = po.manager_id
WHERE po.po_id IS NULL;

Returns only managers who have not been assigned any purchase order.

Equivalent LEFT JOIN rewrite

The same result using LEFT JOIN by swapping table order:

SELECT
  am.manager_name,
  am.region,
  po.po_number,
  po.total_amount
FROM account_managers am
LEFT JOIN purchase_orders po ON po.manager_id = am.manager_id
ORDER BY am.manager_name;

Both queries produce identical output. The LEFT JOIN version is often preferred because the “primary” table (account_managers) is written first, matching the natural reading order.

RIGHT JOIN vs LEFT JOIN

Testing with Vela

RIGHT JOIN is often used for completeness audits — finding managers with no orders, warehouses with no stock, or catalog items with no transactions. Vela’s database branching lets you run these audit queries against a production-identical snapshot, validate the results, and build follow-up reports or data cleanup scripts without touching live data.

Production Tips

• Any RIGHT JOIN can be rewritten as a LEFT JOIN by reversing table order — standardize on one form across your codebase for readability.
• Index the join column on the left-side table to avoid a sequential scan when the right-side table is large.
• To find right-table rows with no left-table match, filter on WHERE left_table_col IS NULL after the RIGHT JOIN — this is the anti-join pattern.
• Move right-table filter conditions into the ON clause rather than WHERE to avoid accidentally converting the join to INNER JOIN semantics.
• Use EXPLAIN ANALYZE to check whether the planner is reversing the join order internally — PostgreSQL’s optimizer may convert a RIGHT JOIN to a LEFT JOIN anyway for planning purposes.
• Use COALESCE to replace NULL values in aggregations or display columns when right-table rows have no left-table match.