Full Outer Join in SQL Server

In SQL Server, a full outer join is used to combine rows from two tables, where each row in one table is paired with every row in the other table, even if there are no matches. Here's how to perform a full outer join in SQL Server.

Syntax

The syntax for a full outer join is as follows:

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Where:

  • `table1` and `table2` are the two tables to be joined.
  • `column_name` is the common column between the two tables.

Example

Let's say we have two tables, `orders` and `customers`, and we want to perform a full outer join to retrieve all orders and customers, even if there are no matches.

SELECT * FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.customer_id;

This code will return all rows from both tables, with `NULL` values in the columns where there are no matches.

Example Output

Here's an example output of the full outer join:

order_id customer_id order_date customer_name address
1 1 2020-01-01 John Smith 123 Main St
2 1 2020-01-15 John Smith 123 Main St
3 2 2020-02-01 Jane Doe 456 Elm St
NULL 3 NULL Bob Brown 789 Oak St
4 NULL 2020-03-01 NULL NULL

In this example, the `orders` table has three rows, and the `customers` table has three rows. The full outer join returns all five rows, with `NULL` values in the columns where there are no matches.

Using COALESCE to Replace NULL Values

In some cases, you may want to replace the `NULL` values with a default value. You can use the `COALESCE` function to achieve this.

SELECT COALESCE(o.order_id, 0) AS order_id, COALESCE(o.customer_id, 0) AS customer_id, COALESCE(o.order_date, '1900-01-01') AS order_date, COALESCE(c.customer_name, 'Unknown') AS customer_name, COALESCE(c.address, 'Unknown') AS address FROM orders o FULL OUTER JOIN customers c ON o.customer_id = c.customer_id;

This code uses the `COALESCE` function to replace `NULL` values with default values.