So to get started here's our example tables:

Customer Table

Sales Table

The Customer table holds a list of Customers, each with a unique ID. This unique ID is called the Primary Key (PK). The Sales table contains sales data for each customer sale. The table holds a unique ID field, a customer ID field relating to the Customer table (this is a Foreign Key (FK), as it references a PK of another table) and the SalesAmount.

Now, the simplest form of SQL is to retrieve all data from a table, eg:

SELECT * FROM Customer

This query simply returns all the data in the customer table, ie both CustomID and CustomerName fields and all 5 records. Not much use but a good starting point...

Supposing we wanted to see which customer sales associated with them and how much, we would need to join these 2 tables. There are two ways to achieve this, but the most common way is to use inner joins as follows:

SELECT Customer.CustomerName, Sales.SalesAmount 
FROM Customer INNER JOIN Sales ON customer.CustomerID = Sales.CustomerID

The result of this query would be:

Notice the result only contains data where a join exist on CustomerID in both tables.
The SQL above can be written a lot simpler, doing it this way will avoid confusion when other tables are added:

SELECT Customer.CustomerName, Sales.SalesAmount 
FROM Customer, Sales 
WHERE customer.CustomerID = Sales.CustomerID

However, although this may be simpler and the inner join format is easier to convert to an outer join query if required. Outer joins are used to return all records from one or both tables. Assume we want to show all sales so that other results total up to the full sales total then employ outer joins. Outer joins include right joins, left joins and full outer joins. The query below return all sales, even if there isn’t a valid customer associated with them:

SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer 
RIGHT JOIN Sales ON Customer.CustomerID = Sales.CustomerID

Our results will look like this:

Notice we chose to select all the data from the right table in our join statement. This result is useful to highlight omissions and returns Null fields where an inner join would return nothing. What would it look like if we changed to this:

SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer 
LEFT JOIN Sales ON Customer.CustomerID = Sales.CustomerID

Our results:

The results gave us all the records from the left table (Customer) and the linked data from the right, which is a convenient way of reporting on customer sales.

Now, supposing we need to reconcile both tables and wish to return a left and a right join result, then we use full outer joins which return all records of these tables as follows:

SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer 
FULL OUTER JOIN Sales ON Customer.CustomerID = Sales.CustomerID

This would therefore return:

Top of Page

© Ken Howe 2011