Re: Editing disabled by alternative to INNER JOIN

On Sat, 04 Aug 2007 16:25:42 -0700, lyle <lyle.fairfield@xxxxxxxxx>
wrote:

On Aug 4, 6:47 pm, Arch <send...@xxxxxxxx> wrote:
On Sat, 04 Aug 2007 09:28:47 GMT, "'69 Camaro"



<ForwardZERO_SPAM.To.69Cam...@xxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi, Chris.

SELECT * FROM teachers INNER JOIN subjects ON
teachers.name=subjects.taught_by;

but I had typed in the equivalent

SELECT * FROM teachers, subjects WHERE
teachers.name=subjects.taught_by;

These two queries are _not_ equivalent in Jet (Access). The second query
uses a Cartesian Join, instead of an ANSI join. A Cartesian Join matches
every row in one table to every row in the other table (even when there is
no logical correlation). A query that uses a Cartesian Join is not
updateable, that's why you hear the beeps. The fact that you placed a WHERE
clause narrowed the results so that the results of the two queries look
identical. However, if you remove the WHERE clause in both queries and then
compare the results, you'll see two very different result sets. Only the
WHERE clause hides the effects of the Cartesian Join (except for the slower
speed when running the query).

I guess the INNER JOIN
construction makes possible some safety checking.

The INNER JOIN allows updateable recordsets, and is much more efficient in
Jet queries. It's easy to run out of resources when using a Cartesian Join
with larger data sets. They take a longer time for Jet to run than when
using an INNER JOIN, too.

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blogs:www.DataDevilDog.BlogSpot.com,www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.htmlfor contact
info.

I don't believe that this particular example is a "Cartesian Join".
Both forms of the query create a valid ANSI join and are valid ANSI
syntax. The first example uses the newer SQL/92 syntax, the second
uses the older SQL/88 syntax.

We've known for a long, long time that such joins are not updatable.
This quote from Microsoft Jet Database Engine Programmer's Guide dates
back more than ten years. It's reproduced at:

http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx?mfr=true

"Updatability Restrictions for Multiple-Table Queries

To be fully updatable, a query must meet several requirements:·

You must specify an explicit inner or outer join between tables. Joins
created implicitly in the WHERE clause of the SELECT statement aren't
updatable. For example, the following join isn't updatable:

SELECT Products.ProductID, Products.ProductName,
Categories.CategoryID, Categories.CategoryName FROM Categories,
Products WHERE Products.CategoryID = Categories.CategoryID;"

In my opinion it's sound practive to use the word "JOIN" when one is
creating a Join. It may add clarity, even for myself when I revisit
some work. Explicit Joins may be faster in some cases than inplicit
Joins. I recommend against the implict JOIN created by a Where clause.

same reference

"Joining Tables

The power of a relational database system is evident when you combine
results from one table with those from another. Microsoft Jet is
flexible in the type and format of the SQL statements used to create a
relational join between tables. In addition to joins in which the
linking criteria is specified in the WHERE clause, Microsoft Jet SQL
can use the JOIN clause to specify inner joins, left and right outer
joins, and self-joins, as well as non-equi-joins, in which the linking
criterion isn't a match in values between two tables, but a generic
conditional expression that evaluates to True. Microsoft Jet also
supports the UNION statement, which concatenates the results of two or
more SELECT statements.
Joins Specified in the WHERE clause

With Microsoft Jet, you can create relational joins in SQL by
specifying the linking condition in the WHERE clause:

SELECT Products.ProductID, Products.ProductName,
Categories.CategoryID, Categories.CategoryName FROM Categories,
Products WHERE Products.CategoryID = Categories.CategoryID;

This query uses two fields each from the Products and Categories
tables and selects records from each table where the CategoryID field
in Products matches the CategoryID field in Categories.
The INNER JOIN

The SQL query produced by the query design grid in Microsoft Access
uses the INNER JOIN clause to join two tables. Here is the same query
as produced by Microsoft Access:

SELECT Products.ProductID, Products.ProductName,
Categories.CategoryID, Categories.CategoryName FROM Categories INNER
JOIN Products ON Categories.CategoryID = Products.CategoryID;

The INNER JOIN names the linking criterion used to find matches
between the two tables. This is the preferred 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, Microsoft Jet requires the use
of this format in order for the results of this join to be updatable.

See Also For a full discussion of query updatability, see Chapter 5,
"Working with Records and Fields."

Many people also prefer this format because it's self-documenting,
unlike the implicit join specified in the WHERE clause. The WHERE
clause is then reserved for selection criteria, rather than doing dual-
duty as a join specifier:"

I think that it's quite helpful for Gunny to say Cartesian Join,
because, until the Where clause is applied, that's what we have. I
suppose tecnnically after the Where Clause is applied we no longer
have the full Cartesian Join, and we have something approaching a
JOIN. If it were entirely equivalent, it would be updatable.

Lyle,
I made no suggestion that the two statements would be treated
identically by Jet. Clearly Jet prefers the newer syntax. Only that
both are valid ANSI syntax and neither is what Gunny called a
"Cartesian Join" (more correctly called a Cartesian product).



.

Relevant Pages

  • Re: Editing disabled by alternative to INNER JOIN
    ... uses a Cartesian Join, ... A query that uses a Cartesian Join is not ... if you remove the WHERE clause in both queries and then ... This quote from Microsoft Jet Database Engine Programmer's Guide dates ...
    (comp.databases.ms-access)
  • RE: Two Different Tables one Query
    ... 1000 rows, a cartesian join will return 100,000 rows! ... A join can be in the FROM clause: ... you should use a naming convention. ... When I start a new query and bring in one table everything works fine. ...
    (microsoft.public.access.queries)
  • Re: Find Records that ovelap
    ... > I want to check for any overlap between records. ... you want a query that will ... no join (Cartesian join), ... (but maybe also add clause to weed out where they ...
    (microsoft.public.access.queries)
  • Re: Can This SQL Work in Access/Jet?
    ... this doesn't create a cartesian join in Oracle; it's the legitimate way to create a join versus the method used in the from clause in Jet. ...
    (comp.databases.ms-access)
  • Bitwise Comparison in WHERE Clause
    ... Can a bitwise comparison be made in the WHERE clause of a SELECT statement using ADO and the OLE DB Provided for Microsoft Jet? ...
    (microsoft.public.data.ado)