AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

Left Outer Joins or simply left joins

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Left Joins

List all customers whether they have orders or not

Discussion:

There are cases in which we do not want to retrieve only the matching records from two related tables.  For instance, we might want to list all customers from the customers table and fields from their associated orders where they exist.  In this case, the output of our query will list all of the records from the customers table and will list the OrderDate and ShippingCost values in the orders table for customers who have orders.  In addition, for customers without orders, it will return blank values for the OrderDate and ShippingCost fields.  The figure below shows the query design for an inner join, which is the default join type when we create a query in Access 2007.

Double-click on the relationship line between customers and orders.  The Joins dialog box comes up.  Change the join type to number 2: "Include ALL records from Customers and only those records from Orders where the joined fields are equal". 

The query design now changes to:

A left join will output all of  customers from the customers table and any related fields from the orders table based on matches on the CustomerID field.  If there are any customers without orders, they will still appear in the result set.  If there are any orders without associated customers, they will not appear in the result set.  Notice the relationship arrow that now has a point toward the Orders table.  This is the visual sign that there is a left join relationship between these two tables. 

Code:

SELECT lastname, firstname, Address, OrderDate, ShippingCost

FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

 

Result:

Note that the database returned 1011 records.  This is because there are 1000 records in which the CustomerID in the customers table has an associated CustomerID in the orders table.  In addition, we have 11 customers without a CustomerID in the orders table, but they will appear in the result set because this is a left join.  Notice the blank values for the OrderDate and ShippingCost for some of the customers without any orders.