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.

  

 

 

Understanding Inner Joins

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Inner Joins

Find customers who actually have some orders

Discussion:

Let us assume that our supervisor has a very simple request:  She wants a report of customers who actually ordered something from us.  By the way, the customers table might include people who asked for quotations or leads, or it might include customers who have not ordered anything for some time.  How can we answer this request?  Of course, we can go back to the chapter about duplicate, orphaned, and related records and use a subquery such as: 

Code:

SELECT *

FROM Customers

WHERE CustomerID

IN (SELECT CustomerID from tbls_Orders)

 

Result:

Notice that the number of customers returned is only 190.  However, in the customers table, there are 201 customers.  So, 11 customers have not had any orders at all for the historical data we have.

Why, however, should be use a subquery since we can do the same thing in no time using a join?  Let’s create a new query in design view and add the customers and orders tables.  There is a one-to-many relationship between customers and orders.  For one customer, there might be multiple orders, but each order definitely belongs to one customer. 

Double-click on the relationship line between customers and orders.  The following dialog box comes up.  By default, Access 2007 will join two tables through an inner join or, in other words, include records from each table where there is a common CustomerID field.  The CustomerID field is the primary key in the customers table and the foreign key in the orders table.

This is the case of an inner join.  Notice that an inner join will give us the customers who have orders.  If there are any customers without orders, they will not appear in the result set.  In addition, if there are any orders without associated customers, they will not appear either.  Only where the two tables match on the CustomerID will records be returned.  The SQL code for the inner join appears below.  Notice that the database returned 1000 records.  This is because there are 1000 cases in which the CustomerID in the customers table has an associated CustomerID in the orders table.  The eleven customers without a CustomerID in the orders table will not appear in the result set of this inner join.  

Code:

SELECT FirstName, LastName, Address, OrderDate, ShippingCost

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

 

Result:

 

Discussion:

The initial request, however, was to present a list of unique customers who have orders.  We do not need any repeated customer names in the result set.  To achieve this, we need an inner join and a GROUP BY clause by last name, first name, and address fields.  Notice how the number of customers returned is the same as that returned by the subquery we used in the beginning of this example. 

Code:

SELECT FirstName, LastName, Address

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

GROUP BY FirstName, LastName, Address

 

Result: