T-SQL 101: 106 Using LEFT OUTER JOIN

The next type of join that I want to discuss in these posts is a left outer join. We use this mostly, when we know that not all rows are matching.

For example, in the query shown above, I wanted to add up the ordered quantity values for all products. The problem is that if I used an INNER JOIN, I would only get products that match at least one order line.

In this case, I want all products included in the returned values, even if there were no order lines for the products.

With a left outer join, I get at least one row returned for each row in the left-hand table, in this case that’s Products. Where there are no matching rows in the OrderLines table, any column that is used in the query is returned as NULL.

In the example, you can see that for ProductID 68, there were no order lines. The total for ordered quantities is returned as NULL. When a row for ProductID 68 is returned, there is no matching OrderedQuantity value, so NULL is returned, and the SUM of a single row that’s NULL, is itself NULL, and that’s what’s returned for TotalOrders.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-02