SQL Joins Explained: INNER, LEFT, RIGHT, FULL, CROSS, and Self Joins

-- Returns only rows where BOTH tables have a match
SELECT u.name, o.total, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- Result: only users WHO HAVE orders
-- Users without orders: NOT included
-- Orders without users: NOT included (shouldn't happen with FK)

-- Multiple joins:
SELECT u.name, p.title, c.text
FROM users u
INNER JOIN posts p ON p.author_id = u.id
INNER JOIN comments c ON c.post_id = p.id
WHERE u.active = true
ORDER BY p.created_at DESC;

-- All users, with their orders (NULL if no order)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- Users with 0 orders: order_count = 0 (COUNT ignores NULLs)

-- Find users WITHOUT orders:
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;  -- NULL means no matching order

-- LEFT JOIN = LEFT OUTER JOIN (same thing)

-- FULL OUTER: all rows from both tables
SELECT a.id AS a_id, b.id AS b_id
FROM table_a a
FULL OUTER JOIN table_b b ON b.ref_id = a.id;
-- Rows only in a: b columns are NULL
-- Rows only in b: a columns are NULL

-- CROSS JOIN: cartesian product (every combo)
SELECT size, color
FROM sizes CROSS JOIN colors;
-- 5 sizes × 8 colors = 40 rows
-- Use case: generate all combinations (product variants)

-- Employee manager hierarchy
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- LEFT JOIN: include employees without managers (CEO)

-- Find users who made orders on same day as another user:
SELECT DISTINCT a.user_id, b.user_id, a.order_date
FROM orders a
JOIN orders b ON a.order_date = b.order_date
  AND a.user_id != b.user_id;