[libreoffice-users] Re: SQL statement in Base query

Am 23.07.2012 23:54, Dan wrote:
> Ferry Toth wrote:
> > Did you try with another database as a backend? I like postgres as a
> > backend a lot.
> >
> > Ferry
>
>       I am writing a chapter of the Base Guide that includes using SQL
> in the Query Design dialog. Chapter 8 of the Base Guides covers using
> HSQLDB 2.2.8, MySQL, and PostgreSQL as back ends. So, I am not there
> quite yet. It seems that because Base uses HSQLDB 1.8, it does not
> conform with the user guide for the database engine it uses.
>       When I get to that point, I will need to research how each of
> these backends use SQL.
> --Dan
>
> >

Hi,

SELECT <field list> FROM A CROSS JOIN B
 ... is a more explicit way to say...
The older and more common syntax is:
SELECT <field list> FROM A,B
... which creates the cartesian product, which includes all combinations 
of A and B with a row count of COUNT(A.*)*COUNT(B.*)
Both syntax variants can be limited by a WHERE clause.
In the 90ies such queries where not exactly equivalent to INNER JOINs. A 
cross join with WHERE clause used to be much slower because it created 
the full cartesian product first and then filtered out the unwanted 
rows. Only the [INNER] JOIN ... ON... syntax triggered certain 
optimizers. Today this is not an issue anymore. At least HSQLDB performs 
equally well with any syntax variant

SELECT <field list> FROM A CROSS JOIN B WHERE A.BID=B.ID
SELECT <field list> FROM A, B WHERE A.BID=B.ID

SELECT <field list> FROM A JOIN B ON A.BID=B.ID
SELECT <field list> FROM A INNER JOIN B ON A.BID=B.ID

HSQLDB supports all of these with equal results and performance. Base 
parses them correctly.

The ultimate SQL reference for all the common database engines:
> http://kb.askmonty.org/en/cartesian-filter-join-ii-cross-join/



--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted