JOIN Question

[Smartin]

Using A97 & up, is there a difference in how these table joining
constructions are executed?

SELECT *
FROM TblA INNER JOIN TblB ON TblA.ID = TblB.ID;

VS.

SELECT *
FROM TblA, TblB
WHERE TblA.ID = TblB.ID

?

FWIW I grew up learning the former, but have found Oracle's
implementation of the latter to be much easier to read, especially when
multiple tables are joined.

Thanks for any insight,

 

[david epsom dot com dot au]

The first is the 'new style', of which Access was one of
the first examples. All modern programs, including Oracle,
now support the new style, as described in the ANSI standard.

As a program built from the start around the new standard
(long before it was standardised), Access has much better
support for the new style than for the old style. You are
more likely to use indexes correctly, which also means that
you are more likely to have an updatable recordset.

In contrast, Oracle is a direct descendent of the first
SQL database.

(david)

 

[Guest]

Try using the JETShowPlan registry setting and examine the results. For more
information on this topic, see the following link:

http://builder.com.com/5100-6388-5064388.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Arvin Meyer [MVP]]

Actually Oracle was the first relational database. The first example uses
the JOIN statement language, which is more efficient and much faster than a
simple WHERE clause.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Craig Alexander Morrison]

I thought it was the Relational System (later rebadged as DB2).

At the time Chris Date and Ted Codd were working for IBM.

Oracle was based more around SQL (as indeed was DB2 much to the regret of
Codd and Date) than Relational, they are not the same thing.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

 

[Smartin]

Try using the JETShowPlan registry setting and examine the results. For more
information on this topic, see the following link:

http://builder.com.com/5100-6388-5064388.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks for the link I will definitely check that out.

And thanks to all for your responses.

 

[Arvin Meyer [MVP]]

DB2 was and still is missing some of the relational aspects. Oracle made its
debut in 1982, although the company was started in 1979. I believe DB2 came
out in 1983. I won't bet on those dates though.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I thought it was the Relational System (later rebadged as DB2).

At the time Chris Date and Ted Codd were working for IBM.

Oracle was based more around SQL (as indeed was DB2 much to the regret of
Codd and Date) than Relational, they are not the same thing.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

 

[Craig Alexander Morrison]

Yeah my point is SQL is not Relational and Oracle is missing in many areas
too.

It was Date and Codd at IBM around 1974 that got the ball rolling on
Relational Systems.

DB2 Came out for MVS in 1983 but SQL/DS for VSE came out in 1981

If you read Codd or Date all the products are lacking, DB2 is the best of
the lot IMO.

Indeed all the products are lacking in SQL compliance too. Nearly all
implement proprietary supersets of subsets of SQL.

 

[Jamie Collins]

Access has much better
support for the new style [INNER JOIN] than for the old style.

Shame this does not hold true for Access/Jet's OUTER JOIN syntax

Jamie.

--

 

[Guest]

Access was very good for 1994. In general, it can be regarded
as a legacy product.

(david)

Access has much better
support for the new style [INNER JOIN] than for the old style.

Shame this does not hold true for Access/Jet's OUTER JOIN syntax

Jamie.

 

[Jamie Collins]

Access was very good for 1994. In general, it can be regarded
as a legacy product.

Jet 4.0 was very good for 2000. Indeed, some of the Jet 4.0
implemetation remain better than SQL Server 2005 e.g. ability to
reference other rows in the same/other table(s) in CHECK constraints
and better detection of CASCADE cycles in DRI, unlimited parameters to
stored procs, etc. However, if the Access team will not fix simple
(high annoyance factor) bugs then I agree Jet is a legacy product

Jamie.

--

 

[Smartin]

Thanks for the link I will definitely check that out.

And thanks to all for your responses.

It sounds good, but I can't seem to get JETSHOWPLAN to work. I set up
the registry key, rebooted, tried a few queries, but can't find a
SHOWPLAN.OUT file anywhere.

The machine is running A97 on XP Pro.

Any ideas?