There are 4 types of Joins you can use in queries:



  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • CROSS JOIN


First of all, tables do not have to be involved in relationships in order to be included in join statements when you write queries. If you did not know that, now you do!

So I have a small business template that I use for examples, so the code here will be taken from that. Inner Joins are easy because Access creates them automatically for you if your query tables are related. Inner joins are so common though, that I don't believe there's ever really a special need for them. Unless of course you need to join multiple tables to get information about one primary record. Consider this query:

Code:
SELECT customers.customer, orders.orderid, items.category, 
(orders.unitprice * orders.quantity) AS [Sale Price], orders.orderdate

FROM carriers INNER JOIN (items INNER JOIN (customers INNER JOIN orders ON customers.customer = 
orders.customer) ON items.item = orders.item) ON carriers.carrier = orders.carrier 

ORDER BY customers.customer, orders.orderdate
That will give me my sales, when I sold the packages and who delivered them to the customers. This is a common setup for table relationships. The ones in this example are:

  • customers ------> orders (1 to many)
  • items ------> orders (1 to many)
  • carriers ------> orders (1 to many)


As far as I can think of, there really is no special reason for using Inner Join queries, because they're basically used to join data together and not exclude specific data that you want to target. Left and Right joins are mostly used for the purposes of targeting broken records that shouldn't belong in the database. But Left Joins are more useful for gather information about idle activity rather than debugging. For instance, consider this Left Join query:

Code:
SELECT customers.customer, orders.orderid

FROM customers LEFT JOIN orders ON customers.customer = orders.customer 

WHERE orders.orderid IS NULL;
This will tell you what customers have no orders on file. I'm sure this is very common with large companies, because if the people in the sales department look at a query like this, they know right away that you are a prospect that has called their company at one point in time but didn't buy anything. I may be wrong, but I believe the only useful thing to remember about Left and Right joins is to include the WHERE [field] IS NULL portion of the statement. You always want to include this statement where it applies to the fields in the joined table only. If you don't include this portion of the SQL statement, the join is essentially pointless because it will return the same records as an Inner Join will. So to summarize, the business purpose of a Left Join query should, for all practical purposes, be executed to find primary data records that have no activity on them. Again, the prime example would be customer records that don't have any other data records associated with them (no orders on file).

And then we take the Right Join, which serves the exact opposite purpose as the Left Join. The business purpose of this join should really be to find broken associations in your database. Example - orders that have been taken that are not assigned to a customer. I shouldn't say this here, but if your database is designed and/or coded correctly, this would never happen. But, it does happen of course. So, in my example I would write this to find all the orders that have been entered that don't have a customer assigned to them:

Code:
SELECT orders.orderid, customers.customer

FROM customers RIGHT JOIN orders ON customers.customer=orders.customer

WHERE customers.customer IS NULL;
Notice that my Parent table (customer) now has the WHERE [field] IS NULL portion of the statement. And the same rule is true for Right Joins as it is for Left Joins. If you don't include this portion of the statement, the join is essentially pointless again because it will return the same records as an Inner Join will.

So now we get to the interesting part....Cross Joins! I've actually never seen anyone use this, for good reason I'm sure. But if you're curious, the number of records that a Cross Join produces can be calculated simply by multiplying the number of records in each table that you're query is using. For instance, if I have this query:

Code:
SELECT customers.customer, orders.orderid

FROM customers, orders;
It is going to produce 180 records, because there are 6 records in [customers] and 30 records in [orders] (6 * 30 = 180). The above query is absolutely useless, but there are actually situations where a Cross Join can serve a purpose. The common one that you can find on the web is when a discount table is configured into a sales database. If you're interested, consider that you have a discount table that looks like this:

Code:
dAmount
5.00%
10.00%
15.00%
20.00%
If I Cross Join my orders table on this discount table to see how much my sales revenue would be if I gave everyone a 10% discount, the query would look like:

Code:
SELECT orders.orderid, (orders.unitprice *orders.quantity) AS [Original Sales Revenue], 
CCur(Round((orders.unitprice *orders.quantity * (1 - discounts.dAmount)), 2)) AS [Discounted Sales Revenue]

FROM orders, discounts 

WHERE discounts.dAmount = .10

ORDER BY orders.orderid
So my Discounted Sales Revenue would show me how much revenue I would lose, if I gave a 10% discount to everyone. Remember though, cross joins are dangerous because they're simply an exponential operation. You can cut the record count down by using WHERE clauses and GROUP BY clauses. I would suggest you do that too because Cross Joins, for all practical purposes, are pointless unless they are filtered to meet some specific need of the user.