Loading ...
Sorry, an error occurred while loading the content.

Re: [ib-support] search conditions in OUTER JOIN vs WHERE

Expand Messages
  • Helen Borrie
    ... Correct, it is not so. Actually, search conditions and join conditions are never equivalent , even when using the deprecated implicit join syntax
    Message 1 of 5 , Nov 13, 2002
    • 0 Attachment
      At 03:32 PM 13-11-02 +0100, you wrote:
      >Hello everyone,
      >I am looking for an explanation on the behaviour of search conditions
      >in OUTER JOIN clauses as compared to WHERE clauses.
      >As far as I am aware, search conditions in INNER JOINs are equivalent
      >to search conditions in WHERE clauses. For OUTER JOINs this is
      >apparently not so.

      Correct, it is not so. <g> Actually, search conditions and join conditions
      are never "equivalent", even when using the deprecated implicit join syntax
      (see below).

      >* Is this behaviour documented anywhere?

      Yes, in the Using Firebird manual - also in any respectable manual on
      standard SQL.

      >* Does the SQL standards say anything on this subject?


      >* How can I best explain this to students in my Ib/Fb courses?

      See following description.

      >* Is this a feature or a bug?

      Neither. It's a matter of using correct SQL syntax and not mixing one
      syntax with the other (see below).

      >* Or is it a case for the Firebird documentation project?

      Could be; though it's an SQL matter, not specific to Firebird or any other
      RDBMS that is SQL standards-conformant. (I happen to think that a good SQL
      book is an essential part of the Fb/IB developer's library...)

      Here goes.

      The syntax where you
      SELECT <column list> FROM tablea, tableb, ... is known as implicit join
      syntax, a.k.a. SQL-89 join syntax. In this syntax, join criteria are mixed
      with search criteria. It is applicable ONLY to inner joins. At SQL-89
      there was no standard for outer joins.

      Along came SQL-92, with standards for outer joins and the introduction of
      the JOIN ... ON clause, which applies to both inner and outer joins. This
      syntax is often referred to as "SQL-92 join syntax" or "explicit
      joins". Explicit joins separate join criteria from WHERE criteria. It
      deprecates the old implicit join syntax, although there are still some
      DBMS's around which don't support explicit joins. Firebird (unfortunately)
      supports both.

      The two syntaxes should *not* be mixed. For inner joins, you have the
      option of either implicit or explicit join syntax. For outer joins,
      explicit syntax is the only option.

      Of course, the parser will *accept* statements using mixed syntax, but it
      won't perform a join on any criteria listed in the WHERE clause - hence the
      nonsense results you get from mixing the two syntaxes, or from attempting
      to do outer joins using implicit syntax.

      So it really gets down to showing your students some techniques for
      correctly identifying which of their parameters are join criteria and which
      are search criteria. This sounds like a good thing to teach students. <g>

      The Firebird API supports the passing of parameters for WHERE criteria,
      which is a very useful feature when continually passing the same statement
      with a variety of search criteria. The structure of the query
      specification isn't changed by merely varying the values of search
      criteria, so the statement doesn't need to hit the server to return the
      metadata attributes to the client (a process known as "preparing" the
      query). It's one of the most effective ways to jack up performance in

      However, when you change the values of JOIN criteria, you change the
      structure of the query specification - hence, a reprepare is
      required. When implicit join syntax is used, there is no choice but to
      reprepare each time a parameter value changes, since the parser cannot tell
      whether it is being asked to vary a search value or change the query

      So, as well as being the only option for a query containing outer joins,
      the SQL-92 syntax is going to be the *better* choice for inner joins
      anywhere want to take advantage of variable parameters and surface a
      searching/drilling interface (i.e. the typically optimised client/server

      A tip for you and your students regarding joins: IB and Fb are not equal
      in this area. IB permits a select statement to be submitted without table
      identifiers on column names, which can give rise to ambiguous output that
      often remains undetected unless subjected to rigorous QA. Fb does not
      permit unqualified column names in joined sets *at all* and will barf them
      up noisily. This also provides an interesting learning experience for
      students. <g>

    • Marco Bommelj√©
      Hi Hellen, Arno and others, Thanks for the explanations. You re a big help. It seems that I still do not know the complete SQL-92 standards by heart. (I did
      Message 2 of 5 , Nov 14, 2002
      • 0 Attachment
        Hi Hellen, Arno and others,

        Thanks for the explanations. You're a big help.

        It seems that I still do not know the complete SQL-92 standards by
        heart. (I did read Melton's book and worked thru the SQL Standard
        Handbook by Cannan & Otten.)

        So, to summarize:

        * the WHERE clause is related to the results of FROM clause
        * conditions in the ON are directly related to the table references
        * changed parameters in ON: query is re-prepared
        * changed parameters in WHERE: prepared query is unaffected
        * No more SQL-89 JOINs (ambiguous, inefficient when params change)
        * Always use correlation names in JOINs to avoid ambiguous col refs
        * Choose Firebird.

      Your message has been successfully submitted and would be delivered to recipients shortly.