9. Set operators, CASE, PIVOT and MERGE

SELECT Email FROM Customers UNION     SELECT Email FROM Leads;   -- distinct
SELECT Email FROM Customers UNION ALL SELECT Email FROM Leads;   -- keeps duplicates, faster
SELECT Email FROM Customers INTERSECT SELECT Email FROM Leads;
SELECT Email FROM Customers EXCEPT    SELECT Email FROM Leads;

SELECT FullName, CASE WHEN Salary >= 150000 THEN 'Senior' WHEN Salary >= 80000 THEN 'Mid' ELSE 'Junior' END AS Band FROM Employees;

-- PIVOT
SELECT * FROM (SELECT Region, Quarter, Amount FROM Sales) s
PIVOT (SUM(Amount) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])) p;

-- MERGE (upsert) — target vs source
MERGE dbo.Products AS t
USING dbo.StagingProducts AS s ON t.Sku = s.Sku
WHEN MATCHED AND t.Price <> s.Price THEN UPDATE SET t.Price = s.Price
WHEN NOT MATCHED BY TARGET THEN INSERT (Sku, Name, Price) VALUES (s.Sku, s.Name, s.Price)
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $action, inserted.Sku, deleted.Sku;