Using Joins vice Where-clause? (1 Viewer)

[cnstarz]

I'm fairly proficient with Access (at least I like to think I am) and was reading Allen Brown's page on Optimizing Queries when I saw this statement:

With multi-table queries, use JOINs where possible. JET will execute this faster than a WHERE clause on the foreign key.

Could someone elaborate on this? How else would you create a multi-table query (ruling out subqueries) without the use of Joins? How would you filter the results of a multiple table query without specifying criteria (Where-clause) to filter on? What am I missing or not understanding?

 

[Galaxiom]

Allen didn't make this very clear but I think he means to apply the Where to the PK on the one side of the join rather than the FK on the many side. He doesn't mention this elsewhere on the page.

SELECT *
FROM A
INNER JOIN B
ON A.PK = B.FK
WHERE A.PK = 1

Rather than

SELECT *
FROM A
INNER JOIN B
ON A.PK = B.FK
WHERE B.FK = 1

 

[jdraw]

Perhaps he means this??

A)   Select Customer.*, Order.* from Customer inner Join Order on
 Customer.Id = Order.CustomerID

B)   Select Customer.*, Order.* from Customer,Order 
WHERE Customer.Id = Order.CustomerID

I have used both. B) was quite common in Oracle but A) is very common in Access.

Others, more conversant with the speed intricacies inherent in the dbms, may comment.

 

[Galaxiom]

Alternatively he has made a suggestion in error for something that works in TSQL but is not supported in Jet/ACE.

SELECT *
FROM A
INNER JOIN B
ON A.PK = B.FK
AND A.PK = 1

 

[Galaxiom]

I think jdraw has the right answer. Those of us who have not been in databases so long would not be so likely to think of doing it without the join.

 

[CJ_London]

Although not available in the design grid the join does not have to be =

you can also use >=,<= and (bizarrely) <>

you can't use between, but you can use ...>= ... and ...<=...

e.g.

SELECT *
FROM tbl1 INNER JOIN tbl2 ON tbl1.Dte>=tbl2.startdt and tbl1.Dte<=enddt

 

[jdraw]

A little off topic (but may be of interest)
Here is an interesting one I saw recently

SELECT DISTINCT T1.serialno, T1.location, T2.serialno, T2.location
FROM tablename AS T1, tablename AS T2
WHERE T1.serialno = T2.serialno AND T1.location <> T2.location;

The problem was

i am trying to figure out how to get only the duplicate serial numbers if the location is different.

And I have seen some of the "weird ones" that CJ mentioned.

 

[Galaxiom]

A little off topic (but may be of interest)
Here is an interesting one I saw recently

SELECT DISTINCT T1.serialno, T1.location, T2.serialno, T2.location
FROM tablename AS T1, tablename AS T2
WHERE T1.serialno = T2.serialno AND T1.location <> T2.location;

Yes. Although Access only supports equal joins in the Design View, it supports all kinds of joins in SQL view. You can even use functions, though this slows the query because it must apply the function to every record.

This can often be improved if the number of records can be reduced with a join condition that uses indexed fields.

AFAIK, the only limitation is that the joined tables must appear on each side of the operator.