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.
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.
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:
For unsubscribe instructions e-mail to: email@example.com
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy