WHERE syntax v. JOIN ON syntax

  • Thread starter Thread starter Siegfried Heintze
  • Start date Start date
S

Siegfried Heintze

Why should I use the JOIN ON syntax instead of the WHERE syntax?

In my antique book Jet Database Engine Programmer's guide Page it says (page
119)

"This is the prefered format for specifying joins with Microsoft Jet, for
two reasons.

First, this format makes the join criteria explicit rather than inferring it
from the condition in the where clause. Second, Microsfot Jet requires the
use of this format in order for the results of this join to be updatable."

Updatable? What are they talking about? How do you update a SELECT
statement? Can someone please give me an example that cannot be done with
the old "WHERE" syntax but can be done in the new "INNER JOIN" syntax?

Thanks,
Siegfried
 
Consider these 2 queries (which you can test in Northwind if it helps):

Query1:
=====
SELECT Orders.OrderID,
Orders.CustomerID,
[Order Details].ProductID,
[Order Details].Quantity,
[Order Details].UnitPrice
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID;

Query2:
=====
SELECT Orders.OrderID, Orders.CustomerID,
[Order Details].ProductID,
[Order Details].UnitPrice,
[Order Details].Quantity,
[Order Details].OrderID
FROM Orders, [Order Details]
WHERE [Order Details].OrderID = [Orders].[OrderID];

Your quotation makes the point that:
a) JET will process Query1 (the INNER JOIN) more efficiently, and
b) The results returned by Query1 will be updatable, whereas Query2's result
will not.
 
Why should I use the JOIN ON syntax instead of the WHERE syntax?
Click to expand...

This is a bit of an old chestnut. The example that Celko usually
wheels out in deference of the 'old style' WHERE clause join is for an
n-ary relationship (where n is greater than two) such as

A.x BETWEEN B.y AND C.z

which is tricky to write using the infixed notation (INNER JOIN etc).

I think a convincing counter argument is that most people nowadays
expect to see the infixed notation and that 'old style' joins cause
confusion, and that is a maintenance issue worth avoiding. Coders will
still need to be able to read equivalent joins in the WHERE clause,
though e.g.

SELECT A.ID
FROM A
WHERE EXISTS (
SELECT *
FROM B
WHERE B.ID = A.ID);

Specific to Jet, there are some predicates which Jet does not like in
the ON clause that work fine in the WHERE clause. For example, a join
using a simple BETWEEN construct requires parentheses that sometimes I
can't remember/figure out what Jet is demanding of me and I suspect
there are some constructs which Jet cannot handle at all in the ON
clause e.g. this one from yesterday:

SELECT P1.person_name, S1.score, G1.grade_description
FROM (Scores AS S1
INNER JOIN People AS P1
ON P1.person_name = S1.person_name), Grades AS G1
WHERE S1.score BETWEEN G1.grade_limit_lower AND G1.grade_limit_upper
AND P1.age BETWEEN G1.age_limit_lower AND G1.age_limit_upper

I'd be interested in seeing if anyone can rewrite this using INNER
JOIN using the BETWEEN constructs.

Note that Jet does not support the explicit CROSS JOIN syntax, one
reason why WHERE clause joins may be more prevalent here.

Jamie.

--
 
You don't update a SELECT statement, but you DO, sometimes, want to update
data within a recordset returned by a SELECT statement. Two examples: 1)
You can bind a form to a query (Select statement). If the Join is created
in the Where clause, you wouldn't be able to enter or modify anything in the
form. 2) Similarly, sometimes you want to open a recordset via VBA code
(DAO or ADO). This recordset would not be dynamic, but static, ie
non-updateable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
You don't update a SELECT statement, but you DO, sometimes, want to update
data within a recordset returned by a SELECT statement.
Click to expand...

I think what the source was getting at was a VIEW i.e. an Access Query
object defined using a non-parameterized SELECT statementment which
returns a resultset. VIEWs are either updatable or read-only (can be
verified in the SCHEMA INFORMATION view for VIEWs).

Jamie.

--
 
I think what the source was getting at was a VIEW i.e. an Access Query
object defined using a non-parameterized SELECT statementment which
returns a resultset. VIEWs are either updatable or read-only (can be
verified in the SCHEMA INFORMATION view for VIEWs).
Click to expand...
Is "View" a feature of Access or ADO.NET? It sounds like it is a feature of
Access (and other databases). I've never used a view before. I assume I
create a view by specifying a select statement. Perhaps I can find an
example with a google search.
Thanks,
Siegfried
 
Roger Carlson said:
You don't update a SELECT statement, but you DO, sometimes, want to update
data within a recordset returned by a SELECT statement. Two examples: 1)
You can bind a form to a query (Select statement). If the Join is created
in the Where clause, you wouldn't be able to enter or modify anything in
the form. 2) Similarly, sometimes you want to open a recordset via VBA
code (DAO or ADO). This recordset would not be dynamic, but static, ie
non-updateable.
Click to expand...

I assume what you say is true for VB.NET/ADO.NET data sets too?

Does anyone know how the udpate feature in DAO/ADO/ADO.NET
datasets/recordsets work? Do they parse the SELECT statement that (I
presume) is stored in the dataset/recordset and fabricate the appropriate
INSERT/UPDATE statements and execute those statements automagically? If so,
is it simply a matter of the parsers not being smart enough to parse and
update the result set of a SELECT that uses WHERE instead of INNER JOIN?

Thanks,
Siegfried
 
The JOIN syntax is not a parsing issue, it is an interpretation issue.
with the non-specific join using the where clause, Jet assumes that you want
a Cartesian Product (cross join) and Cartesian Products are not updatable.

As far as the non-equ-join, Jet supports them but not with the QBE. If you
use such a join, you need to work in SQL view.

Select ...
From tblA Inner Join tblB on tblA.fld1 between tblB.fld2 AND tblB.fld3;
This join will produce an updatable recordset unless something other than
the join prevents it.
 
Is "View" a feature of Access
Click to expand...

http://en.wikipedia.org/wiki/View_(database)
"In database theory, a view is a virtual or logical table composed of
the result set of a query..."

http://office.microsoft.com/en-gb/access/HP010441931033.aspx#idh_dadefview
"View is the ANSI definition of a virtual table. A view is synonymous
to a stored query in Access that is comprised of a SELECT statement
without parameters. A view is a stored definition that is materialized
at execution. It is different from a table in that it does not
physically store any data. It returns data only.A view can be created
from the new ANSI SQL CREATE VIEW syntax"

http://office.microsoft.com/en-gb/access/HP010322231033.aspx
Microsoft Office Access 2003: CREATE VIEW Statement (Microsoft Jet
database engine)
I've never used a view before.
Click to expand...

You probably have but you probably called it a Query (object) :) The
trouble is, 'Query' is too vague a term to be useful in context
because a Query can be defined using any SQL statement, be it SQL DML,
SQL DDL or even SQL DCL e.g. the following is a valid Jet SQL
statement (madness! <g>) and successfully creates an Access 'Query'
object:

CREATE PROCEDURE Proc1 AS
CREATE PROCEDURE Proc2 AS
CREATE PROCEDURE Proc3 AS
ROLLBACK TRANSACTION;

Jamie.

--
 
Back
Top