Types of Joins in T-SQL

When working with databases in T-SQL, there are several types of JOINs that can be used to retrieve data from multiple tables. Each type of JOIN serves a different purpose and understanding how each one works is essential for writing efficient queries.

1. INNER JOIN

An INNER JOIN returns rows from both tables that have matching values based on a specified condition in the ON clause. If there is no match, the rows are not returned in the result set.

2. LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for the columns from the right table.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN is the opposite of a LEFT JOIN, returning all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for the columns from the left table.

4. FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for the columns from the table without a match.

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, meaning that every row from the first table is combined with every row from the second table. This can result in a very large result set if not used carefully.

By understanding the different types of JOINs in T-SQL and how they work, you can write more efficient queries that retrieve the desired results from your database tables.