Joining database tables is a common task in SQL Server. It allows you to combine data from multiple tables based on a related column. In this article, we will explore different types of joins in SQL Server and how to use them effectively.
Inner Join
The inner join returns only the matching rows between two tables. It combines rows from both tables where the join condition is true. Here is an example of an inner join:
SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.Column = Table2.Column;
Left Join
The left join returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table. Here is an example of a left join:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column = Table2.Column;
Right Join
The right join returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table. Here is an example of a right join:
SELECT *
FROM Table1
RIGHT JOIN Table2
ON Table1.Column = Table2.Column;
Full Join
The full join returns all the rows from both tables. If there is no match, NULL values are returned for the non-matching rows. Here is an example of a full join:
SELECT *
FROM Table1
FULL JOIN Table2
ON Table1.Column = Table2.Column;
Cross Join
The cross join returns the Cartesian product of both tables. It combines each row from the first table with every row from the second table. Here is an example of a cross join:
SELECT *
FROM Table1
CROSS JOIN Table2;
These are the basic join types in SQL Server. By understanding how each join works, you can effectively retrieve and combine data from multiple tables. Experiment with different join types to see how they affect your query results.