+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

difference between explicit inner join and implicit

  1. difference between explicit inner join and implicit

    Is there any difference between explicit inner join and implicit
    inner join

    Example of an explicit inner join:

    SELECT *
    FROM employee
    INNER JOIN department
    ON employee.DepartmentID = department.DepartmentID

    Example of an implicit inner join:

    SELECT *
    FROM employee, department
    WHERE employee.DepartmentID = department.DepartmentID

  2. Re: difference between explicit inner join and implicit

    On Mar 31, 1:02 pm, YZXIA wrote:
    > Is there any difference between explicit inner join and implicit
    > inner join


    The execution plan for both queries is the same, so that suggests to
    me that they are functionally identical.

  3. Re: difference between explicit inner join and implicit

    >> Is there any difference between explicit [sic: infixed] inner join and implicit inner join <<

    In theory, yes; in practice, no. The SQL-92 Standard says that the
    infixed join notation is executed in left-to-right order in the FROM
    clause. But the optimizer is free to re-arrange the query as long as
    the results are the same.

    This does not make a difference with INNER JOINs, but it is vital with
    OUTER JOINs.

    There is a good story about this proposal. We needed the OUTER JOIN
    syntax; the proprietary methods in Oracle, SQL Server, Sybase,
    Informix, etc. were all screwed up. But after we had defined the
    OUTER JOIN, it was easy to extend the paper to INNER JOIN, UNION JOIN,
    NATURAL JOIN, etc. (most of which nobody implements).

    The rationale after the fact was that a product could have an option
    to force an order of execution with the infixed notation. Since SQL
    products now have some kind of optimizer, it is not really useful.

    The real difference is in the mindset of programmers. Those that
    write with infixed notation thing in terms of a linear sequence of
    joins, as if they were limited to simple binary Theta operators. The
    programmers that use the older notation will use BETWEEN, IN () and
    other predicates that work with multiple terms.

  4. Re: difference between explicit inner join and implicit


    > Is there any difference between *explicit inner join and implicit
    > inner join


    Technically no, but it's a good idea to make it a habit to use the
    explicit inner join. It is considered the standard nowadays, is
    easier to read and debug, and is consistent with the OUTER JOIN
    syntax.


  5. Re: difference between explicit inner join and implicit

    --CELKO-- wrote:

    > The real difference is in the mindset of programmers. Those that
    > write with infixed notation thing in terms of a linear sequence of
    > joins, as if they were limited to simple binary Theta operators. The
    > programmers that use the older notation will use BETWEEN, IN () and
    > other predicates that work with multiple terms.


    Like this, you mean? And are you recommending or deprecating it?

    select a.x, b.y
    from table1 a
    join table2 b on a.x between b.y and 500

  6. Re: difference between explicit inner join and implicit

    >> Like this, you mean? <<

    No, try this:

    SELECT ..
    FROM A, B, C
    WHERE A.x BETWEEN B.y AND C.z;

    Versus:

    SELECT ..
    FROM A
    INNER JOIN
    B
    ON A.x >= B.y
    INNER JOIN
    C
    ON C.z <= A.x;

    The "between" is lost in what typographers call the law of proximity
    because things are split into binary operators. Likewise, look around
    this newsgroup for

    a = x1 OR a = x2 or a = x3

    versus
    a IN (x1, x2, x3)

    The set of matches is lost in the binary operators. My favorite
    example of this mindset is

    x1 = 0 OR x2 = 0 OR x3 = 0
    versus
    0 IN (x1, x2, x3)

    Because the binary mindset is a left-to-right English based ordering
    that says "x is zero" and "zero is x" when coding. I used to run into
    this difference with Arab and Asian students who did not have this
    cultural baggage.




  7. Re: difference between explicit inner join and implicit

    >> Technically no, but it's a good idea to make it a habit to use the explicit inner join. It is considered the standard nowadays, <<

    Not when I was on the Standards Committee; did you join later than
    me?

    >> is easier to read and debug, <<


    NO, it isn't; have you seen any of the human factors research? The ON
    clauses can be spread so far from the matching tables debugging time
    increases. Multiple parameter predicates like BETWEEN and IN are
    split and their higher level meaning is lost.

    But it looks like ACCESS and has a nice binary operator feel that
    procedural programmers like.

    >> and is consistent with the OUTER JOIN syntax. <<


    Yes, that is the reason it exists.


  8. Re: difference between explicit inner join and implicit

    On Tue, 1 Apr 2008 07:01:14 -0700 (PDT), --CELKO-- wrote:

    >>> Like this, you mean? <<

    >
    >No, try this:
    >
    >SELECT ..
    > FROM A, B, C
    > WHERE A.x BETWEEN B.y AND C.z;
    >
    >Versus:
    >
    >SELECT ..
    > FROM A
    > INNER JOIN
    > B
    > ON A.x >= B.y
    > INNER JOIN
    > C
    > ON C.z <= A.x;
    >
    >The "between" is lost in what typographers call the law of proximity
    >because things are split into binary operators.


    Hi Joe,

    And both versions hide the actual relationship between B and C.

    SELECT ..
    FROM B
    INNER JOIN C
    ON C.z >= B.y
    INNER JOIN A
    ON A.x BETWEEN B.y AND C.z;

    Now we have the "between", *and* the relationship between B and C is
    shown explicitly. Looks like a winner to me!

    (Though I have to admit that I'd be hardpressed to come up with an
    example that makes it even vaguely believeable that a query such as this
    would ever show up in the real world).

    --
    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

  9. Re: difference between explicit inner join and implicit

    >> Now we have the "between", *and* the relationship between B and C is shown explicitly. Looks like a winner to me! <<

    No, no! What you have done is hide redundancy which would have been
    clearly shown in a simple WHERE clause. I don't think I am the only
    guy who puts all the WHERE clause predicates into a tool to sort them
    to see exactly what predicates go to which tables.


    >> (Though I have to admit that I'd be hard pressed to come up with an example that makes it even vaguely believable that a query such as this would ever show up in the real world). <<


    LOL! We have seen much worse in this newsgroup!

  10. Re: difference between explicit inner join and implicit

    If anyone is willing to help a novice,

    is there any difference between the JOIN operator and the INNER JOIN
    operator?

    For example, in a sample database, I get identical results when I run

    SELECT title, name
    FROM books JOIN publisher
    USING (pubid)

    and

    SELECT title, name
    FROM books INNER JOIN publisher
    USING (pubid)

    Thanks,

    Neil

    *** Sent via Developersdex http://www.developersdex.com ***

+ Reply to Thread
Page 1 of 2 1 2 LastLast