1. INNER JOIN
Returns only the records that have matching values in both tables.
SELECT columns
FROM table_a
INNER JOIN table_b
ON table_a.key = table_b.key;
2. LEFT JOIN (LEFT OUTER JOIN)
Returns all records from the left table and matched records from the right table.
SELECT columns
FROM table_a
LEFT JOIN table_b
ON table_a.key = table_b.key;
3. RIGHT JOIN (RIGHT OUTER JOIN)
Returns all records from the right table and matched records from the left table.
SELECT columns
FROM table_a
RIGHT JOIN table_b
ON table_a.key = table_b.key;
4. FULL JOIN (FULL OUTER JOIN)
Returns all records when there's a match in either left or right table.
SELECT columns
FROM table_a
FULL OUTER JOIN table_b
ON table_a.key = table_b.key;
5. LEFT JOIN (Excluding matches)
Returns records from the left table that don't have matches in the right table.
SELECT columns
FROM table_a
LEFT JOIN table_b
ON table_a.key = table_b.key
WHERE table_b.key IS NULL;
6. RIGHT JOIN (Excluding matches)
Returns records from the right table that don't have matches in the left table.
SELECT columns
FROM table_a
RIGHT JOIN table_b
ON table_a.key = table_b.key
WHERE table_a.key IS NULL;
7. CROSS JOIN (Cartesian Product)
Returns the Cartesian product of both tables (every row from table A combined with every row from table B).
SELECT columns
FROM table_a
CROSS JOIN table_b;
8. SELF JOIN
A join where a table is joined with itself. Uses table aliases to distinguish between the two instances.
SELECT a1.columns, a2.columns
FROM table_a a1
JOIN table_a a2
ON a1.key = a2.foreign_key;