RIGHT JOIN, FULL JOIN, and Cross Joins

~10 min · queries, joins

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

RIGHT JOIN is just LEFT JOIN backwards

RIGHT JOIN preserves all rows from the right table. In practice, almost no one writes RIGHT JOIN — flip the table order and use LEFT JOIN, which is more natural to read (the table you care about comes first).

FULL JOIN — keep both sides

FULL JOIN preserves every row from both tables, with NULLs filling in wherever a match is missing. The killer use case is data reconciliation: comparing two datasets where you want to see what's missing on each side.

CROSS JOIN — every row paired with every row

CROSS JOIN produces the Cartesian product. Useful for generating combinations (every product × every region) or for filling in missing rows in time-series. Skip it if you don't need it; an accidental CROSS JOIN is how you ship a 50M-row query.

Code

FULL JOIN for reconciliation·sql

SELECT
    a.date AS accounting_date,
    a.revenue AS accounting_revenue,
    s.date AS sales_date,
    s.revenue AS sales_revenue
FROM   accounting_report a
FULL JOIN sales_report  s ON a.date = s.date
WHERE  a.date IS NULL OR s.date IS NULL;
-- Returns dates that exist on only one side.

CROSS JOIN to generate combinations·sql

-- Every (product, region) pair, even those with no sales
SELECT p.name, r.name, COALESCE(SUM(s.amount), 0) AS revenue
FROM   products p
CROSS JOIN regions r
LEFT JOIN sales s ON s.product_id = p.id AND s.region_id = r.id
GROUP  BY p.name, r.name;

External links

PostgreSQL JOIN types

Exercise

Pick a real reconciliation problem — two tables that should have matching rows. Write a FULL JOIN that surfaces the discrepancies. Run it; investigate one row that surprises you.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousLEFT JOIN and the Orphan Finder Next →GROUP BY and Aggregate Functions

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.