Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

JOIN Question

Discussion in 'Information Technology' started by Smartin, Jul 28, 2006.

Tags:
  1. Smartin

    Smartin
    Expand Collapse
    Guest

    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,
    --
    Smartin
     
  2. Loading...

    Similar Threads Forum Date
    Seema Munde Joins Sikh Philosophy Network! New SPN'ers Thursday at 8:59 AM
    Prem K Jo Joins Sikh Philosophy Network! New SPN'ers Tuesday at 3:35 PM
    Thalinder Singh Joins Sikh Philosophy Network! New SPN'ers Monday at 11:01 AM
    Gurinder Singh Bhujang Joins Sikh Philosophy Network! New SPN'ers Nov 27, 2016
    Surinder Singh Khalsa Joins Sikh Philosophy Network! New SPN'ers Nov 24, 2016

  3. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    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)


    "Smartin" <smartin108@yahoo.com> wrote in message
    news:v-2dnTDqXIXi_ljZnZ2dnUVZ_sGdnZ2d@giganews.com...
    > 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,
    > --
    > Smartin
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "Smartin" wrote:

    > 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,
    > --
    > Smartin
     
  5. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
    news:eFKyhI6rGHA.1272@TK2MSFTNGP05.phx.gbl...
    > 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)
    >
    >
    > "Smartin" <smartin108@yahoo.com> wrote in message
    > news:v-2dnTDqXIXi_ljZnZ2dnUVZ_sGdnZ2d@giganews.com...
    >> 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,
    >> --
    >> Smartin

    >
    >
     
  6. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    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

    "Arvin Meyer [MVP]" <a@m.com> wrote in message
    news:eL9sIg9rGHA.3564@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    > "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
    > news:eFKyhI6rGHA.1272@TK2MSFTNGP05.phx.gbl...
    >> 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)
    >>
    >>
    >> "Smartin" <smartin108@yahoo.com> wrote in message
    >> news:v-2dnTDqXIXi_ljZnZ2dnUVZ_sGdnZ2d@giganews.com...
    >>> 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,
    >>> --
    >>> Smartin

    >>
    >>

    >
    >
     
  7. Smartin

    Smartin
    Expand Collapse
    Guest

    Tom Wickerath wrote:
    > 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.

    --
    Smartin
     
  8. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    "Craig Alexander Morrison" <cam@microsoft.newsgroups.public.com> wrote in
    message news:OnhhWj9rGHA.652@TK2MSFTNGP04.phx.gbl...
    >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
    >
    > "Arvin Meyer [MVP]" <a@m.com> wrote in message
    > news:eL9sIg9rGHA.3564@TK2MSFTNGP03.phx.gbl...
    >> 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
    >>
    >> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
    >> news:eFKyhI6rGHA.1272@TK2MSFTNGP05.phx.gbl...
    >>> 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)
    >>>
    >>>
    >>> "Smartin" <smartin108@yahoo.com> wrote in message
    >>> news:v-2dnTDqXIXi_ljZnZ2dnUVZ_sGdnZ2d@giganews.com...
    >>>> 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,
    >>>> --
    >>>> Smartin
    >>>
    >>>

    >>
    >>

    >
    >
     
  9. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    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.

    --
    Slainte

    Craig Alexander Morrison
    Crawbridge Data (Scotland) Limited
    "Arvin Meyer [MVP]" <a@m.com> wrote in message
    news:ebhrZ7FsGHA.4596@TK2MSFTNGP04.phx.gbl...
    > 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
    >
    > "Craig Alexander Morrison" <cam@microsoft.newsgroups.public.com> wrote in
    > message news:OnhhWj9rGHA.652@TK2MSFTNGP04.phx.gbl...
    >>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
    >>
    >> "Arvin Meyer [MVP]" <a@m.com> wrote in message
    >> news:eL9sIg9rGHA.3564@TK2MSFTNGP03.phx.gbl...
    >>> 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
    >>>
    >>> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
    >>> news:eFKyhI6rGHA.1272@TK2MSFTNGP05.phx.gbl...
    >>>> 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)
    >>>>
    >>>>
    >>>> "Smartin" <smartin108@yahoo.com> wrote in message
    >>>> news:v-2dnTDqXIXi_ljZnZ2dnUVZ_sGdnZ2d@giganews.com...
    >>>>> 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,
    >>>>> --
    >>>>> Smartin
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    david epsom dot com dot au wrote:
    > 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.

    --
     
  11. Guest

    Guest
    Expand Collapse
    Guest

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

    (david)

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153920129.154259.248840@75g2000cwc.googlegroups.com...
    >
    > david epsom dot com dot au wrote:
    > > 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.
    >
    > --
    >
     
  12. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    david@epsomdotcomdotau wrote:
    > 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.

    --
     

Share This Page