A JOIN in SQL Server is used to combine rows from two or more tables based on a related column between them. There are several types of joins, including inner join, left join, right join, and full outer join.

An inner join returns only the rows that have matching values in both tables. A left join returns all rows from the left table and the matching rows from the right table. A right join returns all rows from the right table and the matching rows from the left table. A full outer join returns all rows from both tables, with NULL values in the columns where there is no match.

The join is performed using the ON or USING clause, which specifies the column(s) to be used for the join. For example, the following query retrieves all rows from the "orders" table and the corresponding customer information from the "customers" table, where the customer ID in the orders table matches the ID in the customers table:

SELECT orders.*, customers.*
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;

Join Example:

SELECT s.student_id, s.name, b.book_id, b.price
FROM students s, books b
WHERE s.student_id = b.student_id
AND b.price > 90 ;

Students table:

Books table:

Join Result:

It is also possible to join more than two tables in a single query, and also use a combination of different types of joins.

It's important to be careful about the size of the dataset you are joining and the condition in join statement, as it can lead to poor performance if not handled properly.