Joins in SQL Server are used to combine rows from two or more tables based on related columns between them. Here are the main types of joins:

1. INNER JOIN: Returns only the rows where there is a match in both tables.

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table. If there's no match, the result is NULL from the right side.

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. If there's no match, the result is NULL from the left side.

SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;

4. FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables. This means it returns all rows from the left table and right table, with NULLs where there is no match.

SELECT a.column1, b.column2
FROM table1 a
FULL JOIN table2 b ON a.common_column = b.common_column;

5. CROSS JOIN: Returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows.

SELECT a.column1, b.column2
FROM table1 a
CROSS JOIN table2 b;

6. SELF JOIN: A regular join where a table is joined with itself. It's useful for querying hierarchical data or comparing rows within the same table.

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table1 b ON a.common_column = b.common_column;

These are the most common joins used in SQL Server. Would you like to see an example of a specific join or explore something more complex?