RIGHT JOIN, FULL JOIN, CROSS JOIN and Self-Joins

Beyond INNER and LEFT JOIN, SQL provides three more join types that solve specific problems: RIGHT JOIN (the mirror of LEFT JOIN), FULL OUTER JOIN (all rows from both tables), and CROSS JOIN (every combination of rows). Self-joins — where a table is joined to itself — enable hierarchical and comparative queries like finding posts by the same author or comparing a user’s activity to their peers. While LEFT JOIN handles most real-world use cases, knowing the full toolkit prevents you from writing unnecessarily complex workarounds.

RIGHT JOIN #

-- RIGHT JOIN: all rows from the RIGHT table, NULLs for non-matching left rows
-- Identical to LEFT JOIN with the tables swapped

-- These two are equivalent:
SELECT u.name, p.title
FROM users u RIGHT JOIN posts p ON u.id = p.author_id;

SELECT u.name, p.title
FROM posts p LEFT JOIN users u ON p.author_id = u.id;

-- Convention: rewrite RIGHT JOINs as LEFT JOINs for consistency
-- Most teams ban RIGHT JOIN in favour of always using LEFT JOIN
-- with the "primary" table first

FULL OUTER JOIN #

-- FULL OUTER JOIN: all rows from BOTH tables, NULLs where no match exists

-- Find all users and posts — show orphans on either side
SELECT
    u.id    AS user_id,
    u.name  AS user_name,
    p.id    AS post_id,
    p.title AS post_title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.author_id;
-- Rows where user has no post: user_name present, post_id and post_title NULL
-- Rows where post has no user (orphan): post_title present, user_id and user_name NULL

-- Data reconciliation: find rows that exist in one table but not the other
SELECT
    u.email       AS users_email,
    ext.email     AS external_email
FROM users u
FULL OUTER JOIN external_contacts ext ON u.email = ext.email
WHERE u.email IS NULL          -- in external_contacts but not users
   OR ext.email IS NULL;       -- in users but not external_contacts

CROSS JOIN #

-- CROSS JOIN: every combination of rows (Cartesian product)
-- Rarely used in application queries — mainly for generating combinations

-- All possible colour + size combinations for products
SELECT c.colour, s.size
FROM colours c CROSS JOIN sizes s;
-- If colours has 3 rows and sizes has 4: produces 12 rows

-- Generate a series of dates (using CROSS JOIN with generate_series)
SELECT
    generate_series::DATE AS date,
    COALESCE(COUNT(p.id), 0) AS post_count
FROM generate_series('2025-01-01', '2025-12-31', INTERVAL '1 day') AS gs
LEFT JOIN posts p ON p.created_at::DATE = gs::DATE
GROUP BY gs
ORDER BY gs;

Self-Join #

-- Self-join: join a table to itself using different aliases
-- Use case: find pairs, compare rows, hierarchies

-- Find all pairs of posts by the same author
SELECT
    p1.id     AS post1_id,
    p1.title  AS post1_title,
    p2.id     AS post2_id,
    p2.title  AS post2_title,
    u.name    AS author_name
FROM posts p1
JOIN posts p2   ON p1.author_id = p2.author_id AND p1.id < p2.id
JOIN users u    ON p1.author_id = u.id
ORDER BY author_name;
-- p1.id < p2.id prevents duplicate pairs (A,B) and (B,A) and self-pairs (A,A)

-- Hierarchical: employees and their managers (same table)
-- manager_id references the same users table
SELECT
    e.name       AS employee,
    m.name       AS manager
FROM users e
LEFT JOIN users m ON e.manager_id = m.id
ORDER BY manager NULLS FIRST;

-- Find users who registered on the same day
SELECT
    u1.name AS user1,
    u2.name AS user2,
    u1.created_at::DATE AS registration_date
FROM users u1
JOIN users u2 ON u1.created_at::DATE = u2.created_at::DATE
           AND u1.id < u2.id   -- avoid duplicates and self-pairs
ORDER BY registration_date;

Common Mistakes #

Mistake 1 — Accidental CROSS JOIN from missing ON clause #

❌ Wrong — produces massive result set:

SELECT * FROM posts, users;   -- implicit CROSS JOIN! n_posts × n_users rows

✅ Correct — always specify the join condition:

SELECT * FROM posts JOIN users ON posts.author_id = users.id;   -- ✓

Mistake 2 — Using RIGHT JOIN instead of rewriting as LEFT JOIN #

❌ Wrong — confusing to read when mixed with LEFT JOINs:

SELECT u.name, p.title
FROM posts p RIGHT JOIN users u ON p.author_id = u.id;

✅ Correct — swap table order and use LEFT JOIN:

SELECT u.name, p.title
FROM users u LEFT JOIN posts p ON u.id = p.author_id;   -- ✓ same result, clearer

Mistake 3 — Self-join without eliminating duplicates and self-pairs #

❌ Wrong — returns (A,B), (B,A), and (A,A):

SELECT p1.title, p2.title FROM posts p1 JOIN posts p2 ON p1.author_id = p2.author_id;

✅ Correct — use < to get each pair once:

SELECT p1.title, p2.title FROM posts p1
JOIN posts p2 ON p1.author_id = p2.author_id AND p1.id < p2.id;   -- ✓

Quick Reference #