SQL RIGHT JOIN

-- Setup
-- users: id, name
-- orders: id, user_id, total

-- LEFT JOIN - every user, with their orders (if any)
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- RIGHT JOIN - every order, with the user (if any)
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

-- Most teams write LEFT JOINs instead - it reads more naturally
-- The same result with LEFT JOIN by swapping table order:
SELECT u.id, u.name, o.id AS order_id, o.total
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

-- FULL OUTER JOIN - every row from BOTH sides
SELECT u.id, u.name, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

-- Anti-join via LEFT/RIGHT JOIN + IS NULL
-- 'orders with no matching user' (orphan rows)
SELECT o.*
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

-- 'users with no orders' via LEFT JOIN
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Multi-table example
SELECT u.id, u.name, o.id AS order_id, p.name AS product
FROM users u
LEFT JOIN orders o      ON o.user_id = u.id
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p    ON p.id = i.product_id
WHERE u.country = 'AU';

-- USING - shorthand when columns share names
SELECT id, name, total
FROM users
LEFT JOIN orders USING (user_id);

-- Performance tip
-- LEFT/RIGHT JOIN can hide bad join keys. Always EXPLAIN big outer joins.
-- If you see a Nested Loop with high cost, an index on the join column will help.

SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;