5. Joins

-- INNER: matching rows only
SELECT e.FullName, d.DeptName FROM Employees e INNER JOIN Departments d ON d.DeptId = e.DeptId;

-- LEFT: all employees, department if any
SELECT e.FullName, d.DeptName FROM Employees e LEFT JOIN Departments d ON d.DeptId = e.DeptId;

-- Anti-join: departments with no employees
SELECT d.DeptName FROM Departments d LEFT JOIN Employees e ON e.DeptId = d.DeptId WHERE e.EmpId IS NULL;

-- FULL OUTER: everything from both sides
SELECT * FROM A FULL OUTER JOIN B ON A.Id = B.Id;

-- SELF JOIN: employee and manager
SELECT e.FullName AS Employee, m.FullName AS Manager
FROM Employees e LEFT JOIN Employees m ON m.EmpId = e.ManagerId;

-- CROSS JOIN: every combination
SELECT s.Size, c.Color FROM Sizes s CROSS JOIN Colors c;

-- CROSS APPLY / OUTER APPLY: run a correlated table expression per row (top 3 orders per customer)
SELECT c.Name, o.OrderId, o.Total
FROM Customers c
CROSS APPLY (SELECT TOP (3) OrderId, Total FROM Orders WHERE CustomerId = c.CustomerId ORDER BY Total DESC) o;