What is a Join: Part 4 (Equi-Joins in the WHERE Clause)

Equi-Joins in the WHERE Clause

In my last post in this series on the JOIN (What Is A Join: Part 3 (Cartesian Joins)), I discussed the Cartesian Join, which has very few uses and in most cases should be avoided at all costs. However, there is one practical use for a Cartesian join as long as you restrict its output with a WHERE clause.

So far, I've created the Joins in the FROM clause of the SQL statement. This is standard SQL, but it's also possible to create an Equi-Join in the WHERE clause. As a general rule, it's better to create your join in the JOIN clause, but there are circumstances under which is it useful to do it in the WHERE, which I'll show in just a bit.

To create a Join in the WHERE clause, you create a Cartesian join and add a WHERE clause equating the common fields of both tables. In the Query Builder, add both tables to the table window, but you DON'T create a Join line between them. If the Query Builder adds one automatically, delete it. In the WHERE clause you make the join field of one table equal to the join field of the other.

For instance, if I wanted to create this query:

in the WHERE clause instead, I would do it like so:

Notice there is no Join line between the tables and the OrderID of the OrderDetails table is equal to the OrderID field of the Orders table. The SQL statement looks like this:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID)=[Orders].[OrderID];

Earlier, I showed that without the Where clause, I would get 28 records. However, with the Where clause, I get the same result as I did when I created the join in the FROM clause.

Where Clause Join
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

In general, this is not as efficient as creating the Join in the FROM clause.

Joining fields of different data types

Suppose the join fields in my two tables are different datatypes. Suppose OrderID in the Order table it is numeric and in OrderDetails it is text. This can happen when dealing with external data sources over which you have no control. You can't create a join on fields of different datatypes, so you have to use a conversion function to convert one of the fields. Since OrderID in the Orders table is a Long Integer, I can use the CLng function to convert OrderID in the OrderDetails table.

I can change my original Join clause:

SELECT Orders.OrderNumber, OrderDetails.OrderID,
OrderDetails.Quantity
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

As follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders INNER JOIN CLng(OrderDetails)
ON Orders.OrderID = OrderDetails.OrderID;

This will work. However this cannot be represented in the Design View of the query. If you ever open the query in Design View, it will give you an error and remove the Join line altogether. You must close the query without saving or your query will be ruined.

But if you create the Join in the WHERE clause:

SELECT Orders.OrderNumber, OrderDetails.OrderID,
OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID = CLng([Orders].[OrderID]);

This can be represented in the Design View.

Join Date/Time fields that have times

I have also seen cases where a DateTime field in one table held just the date value, but held date and time in the other table. I needed to use the DateValue function to remove the time from the DateTime in the one table. Again, it was external data over which I had no control.

Creating the Join in the WHERE clause solved the problem nicely. Something like this:

SELECT Patient.MRN, Patient.DischDate,
Billing.Charge
FROM Patient, Billing
WHERE DateValue(Patient.DischDate) = Billing.DischDate
AND Patient.MRN = Billing.MRN;

In my next post, I'll look at Outer Joins.

 

[top]