How to Use Left Outer Joins in SQL (Done)

A LEFT JOIN (also referred to as a LEFT OUTER JOIN) returns all rows from the first table and any corresponding rows from the second table. In cases where a row in the outer(first) table has no matching row in the related table, NULLs are returned as values from the second table.

The basic syntax of LEFT JOIN is as follows:
SELECT table1.column, table2.column FROM table1
LEFT [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

In looking at the above syntax, all rows in table1 will be returned even if they do not have matches in table2. Now consider a join of the Customer table and the SalesOrderHeader table on their CustomerID columns. The results show only the Companies who have orders pending, their SalesOrderID and TotalDue.

SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID;

To include all Companies in the results, regardless of whether they have orders pending or not, we will use a LEFT [OUTER] join. Here is the modified query of the SQL left outer join:

SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID;

///////////////////New Copy Code ////////////////////////// ///////////////////////////////////////

More Examples ...

Below is another outer join example: This query retrieves a list of all customers and any orders they have placed, including customers who have registered but never placed an order.

SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
ORDER BY c.CustomerID;

How to Use Outer Joins in SQL

The following example joins two tables on ProductID and preserves the unmatched rows from the left table. The Product table is matched with the SalesOrderDetail table on the ProductID columns in each table. All products, ordered and not ordered, appear in the result set.

SELECT p.Name,
sod.SalesOrderID
FROM SalesLT.Product AS p
LEFT OUTER JOIN SalesLT.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name;

Note the use of the LEFT keyword in the above two examples. This identifies which of the tables in the join is the "outer" table (the one from which all rows should be preserved).

Using the OUTER keyword in Left Outer Joins

Below is a Left Outer Join that returns the SalesOrderID, ProductName and OrderQty from both the Product and SalesOrderDetail tables.

SELECT od.SalesOrderID, p.Name AS ProductName, od.OrderQty
FROM SalesLT.Product AS p
LEFT OUTER JOIN SalesLT.SalesOrderDetail AS od
ON p.ProductID = od.ProductID
ORDER BY od.SalesOrderID;

Now, modify this query to remove the OUTER keyword, as shown below.

SELECT od.SalesOrderID, p.Name AS ProductName, od.OrderQty
FROM SalesLT.Product AS p
LEFT JOIN SalesLT.SalesOrderDetail AS od
ON p.ProductID = od.ProductID
ORDER BY od.SalesOrderID;

You will notice that the returned result is the same as the first query result with the OUTER keyword. The key take away is that the "OUTER" keyword is optional in OUTER Joins. Using the LEFT (or RIGHT) keyword automatically identifies the join as an OUTER join.

////////STOP//////////// ///////////////////New Copy Code ////////////////////////// /////////////////////////////////////// ///////////////////// PUB Database Examples //////////////////////////// ////Using Left Outer Joins Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). To include all authors in the results, regardless of whether a publisher is located in the same city, use an SQL-92 left outer join. Here is the query and results of the Transact-SQL left outer join: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC ////screenshot ////////

The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column.

///////////End of page ///////////////

.

////////////////////////

Related Content