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: [email protected]
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
