LEFT OUTER JOIN in SQL Server
In SQL Server, a LEFT OUTER JOIN is used to combine rows from two tables where the join condition is met, and also returns all the rows from the left table, even if there are no matches in the right table.
Syntax
The basic syntax for a LEFT OUTER JOIN is:
SELECT column1, column2, ... FROM left_table LEFT OUTER JOIN right_table ON left_table.column_name = right_table.column_name; Example
Let's say we have two tables, `Customers` and `Orders`, and we want to get all the customers, along with their orders, if any.
SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate FROM Customers c LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID; This query returns all the customers, along with their orders, if any. If a customer does not have any orders, the result will contain NULL values for the `Orders` columns.
Result
| CustomerID | CustomerName | OrderID | OrderDate |
|---|---|---|---|
| 1 | John Smith | 1 | 2022-01-01 |
| 1 | John Smith | 2 | 2022-01-15 |
| 2 | Jane Doe | 3 | 2022-02-01 |
| 3 | Bob Brown |
How it Works
The LEFT OUTER JOIN works by:
- Matching rows from the left table (`Customers`) with rows from the right table (`Orders`) based on the join condition (`CustomerID`).
- Returning all the rows from the left table, even if there are no matches in the right table.
- Filling in NULL values for the right table columns if there are no matches.
By using a LEFT OUTER JOIN, we can ensure that we get all the customers, even if they don't have any orders.