INNER JOIN: SELECT e.name,d.name FROM emp e INNER JOIN dept d ON e.dept_id=d.id; : Combine rows that satisfy join condition in both tables

LEFT JOIN: SELECT * FROM a LEFT JOIN b ON a.id=b.id; : Keep all rows from left table, matching from right when present

RIGHT JOIN: SELECT * FROM a RIGHT JOIN b ON a.id=b.id; : Keep all rows from right table, matching from left when present

CROSS JOIN: SELECT * FROM colors CROSS JOIN sizes; : Produce Cartesian product of two tables

NATURAL JOIN: SELECT * FROM t1 NATURAL JOIN t2; : Automatically join tables on columns with same
names

SELF JOIN: SELECT a.name,b.name FROM employees a JOIN employees b ON a.manager_id=b.id; : Join a table to itself to compare rows

USING(): SELECT * FROM orders JOIN customers USING(customer_id); : Shorthand join when column
names are identical

UNION: SELECT city FROM a UNION SELECT city FROM b; : Vertically concatenate result sets removing
duplicates

UNION ALL: SELECT city FROM a UNION ALL SELECT city FROM b; : Combine result sets keeping duplicates for performance

INTERSECT: SELECT id FROM a INTERSECT SELECT id FROM b; : Return rows present in both queries

EXCEPT: SELECT id FROM a EXCEPT SELECT id FROM b; : Return rows from first query absent in second

APPLY (T-SQL): SELECT * FROM a CROSS APPLY dbo.fn(a.id); : Invoke table valued function for each row

Join ON TRUE: SELECT * FROM a LEFT JOIN b ON TRUE; : Emulate FULL OUTER JOIN in MySQL via union of left and right joins

WINDOW clause: SELECT sum(v) OVER w FROM t WINDOW w AS (PARTITION BY c); : Define reusable window specifications

LATERAL: SELECT * FROM t1, LATERAL (SELECT MAX(val) m FROM t2 WHERE t2.id=t1.id) AS x; : Allow
subquery to reference preceding table

MERGE: MERGE INTO dest USING src ON(dest.id=src.id) WHEN MATCHED THEN UPDATE SET ...; :Perform conditional insert/update (upsert) in ANSI SQL or supported engines

PIVOT / UNPIVOT: SELECT ... PIVOT(SUM(amt) FOR qtr IN ('Q1','Q2','Q3','Q4')) AS p; : Rotate rows to columns or vice versa for reporting