RIGHT OUTER JOIN in SQL Server
In SQL Server, a RIGHT OUTER JOIN is used to combine rows from two tables where the join condition is met, and also returns all rows from the right table, even if there are no matches in the left table. This type of join is useful when you want to retrieve all records from the right table, and the matching records from the left table, if they exist.
Syntax
The basic syntax for a RIGHT OUTER JOIN is:
SELECT * FROM left_table RIGHT OUTER JOIN right_table ON left_table.column_name = right_table.column_name; Where:
- `left_table` is the table on the left side of the join.
- `right_table` is the table on the right side of the join.
- `column_name` is the common column between the two tables.
Example 1: RIGHT OUTER JOIN
Let's say we have two tables, `Customers` and `Orders`, and we want to retrieve all customers, along with their corresponding orders, if any:
| Customers | |
|---|---|
| CustomerID | CustomerName |
| 1 | John Smith |
| 2 | Jane Doe |
| 3 | Bob Brown |
| Orders | ||
|---|---|---|
| OrderID | CustomerID | OrderDate |
| 1 | 1 | 2020-01-01 |
| 2 | 1 | 2020-01-15 |
| 3 | 2 | 2020-02-01 |
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; The result set would be:
| CustomerName | OrderID | OrderDate |
|---|---|---|
| John Smith | 1 | 2020-01-01 |
| John Smith | 2 | 2020-01-15 |
| Jane Doe | 3 | 2020-02-01 |
| NULL | NULL | NULL |
Note that the customer "Bob Brown" has no matching order, so the result set returns a row with NULL values for the order columns.
Important Notes
When using a RIGHT OUTER JOIN, keep in mind:
- The right table is the "driving" table, meaning that all rows from this table are returned, even if there are no matches in the left table.
- The left table is the "optional" table, meaning that rows from this table are only returned if there are matches in the right table.