Joining Tables in SQL Server

In SQL Server, joining tables is a way to combine data from two or more tables into a single result set. There are several types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Types of Joins

Here are the different types of joins:

• INNER JOIN: Returns only the rows that have a match in both tables.
• LEFT JOIN: Returns all the rows from the left table, and the matched rows from the right table.
• RIGHT JOIN: Returns all the rows from the right table, and the matched rows from the left table.
• FULL OUTER JOIN: Returns all the rows from both tables, with NULL values in the columns where there are no matches.

INNER JOIN Example

Let's say we have two tables, `Orders` and `Customers`, and we want to join them on the `CustomerID` column.

SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID; 

This query returns only the rows that have a match in both tables, i.e., the orders that have a corresponding customer.

Result

LEFT JOIN Example

Let's say we want to get all the customers, even if they don't have any orders.

SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID; 

This query returns all the rows from the `Customers` table, and the matched rows from the `Orders` table. If there are no matches, the result will contain NULL values for the `Orders` columns.

Result