| Subject | Re: [ib-support] search conditions in OUTER JOIN vs WHERE |
|---|---|
| Author | Helen Borrie |
| Post date | 2002-11-13T23:31:41Z |
At 03:32 PM 13-11-02 +0100, you wrote:
are never "equivalent", even when using the deprecated implicit join syntax
(see below).
standard SQL.
syntax with the other (see below).
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
applications.
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
specification.
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
interface!)
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>
Helen
>Hello everyone,Correct, it is not so. <g> Actually, search conditions and join conditions
>
>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.
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?Definitely!
>* 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
applications.
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
specification.
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
interface!)
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>
Helen