Thread
-
Re: PostgreSQL Advocacy, Thoughts and Comments
cnliou <cnliou@so-net.net.tw> — 2003-11-29T04:37:01Z
"Jason Tesser" <JTesser@nbbc.edu> > MySQL cannot even handle sub-queries yet. Ohh! Really? Allow me to pay my highest respect to the genius mySQL programmers! I completely have no clue on how to construct any single tiny database on a DBMS having no sub-query capability. Being too dumb, I solicit mySQL programmers' help by showing me employee FOO's birthday and his/her latest job title effective on or before 2003-1-1 from the following tables: CREATE TABLE t1 (employee TEXT,BirthDay DATE); CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle TEXT); And make the result like this: FOO 1980-1-1 programmer Please do not give me the answer that you will merge these two tables to form one like this: CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate DATE,JobTitle TEXT); Regards, CN
-
Re: PostgreSQL Advocacy, Thoughts and Comments
Oliver Elphick <olly@lfix.co.uk> — 2003-11-29T07:53:44Z
On Sat, 2003-11-29 at 04:37, cnliou wrote: > "Jason Tesser" <JTesser@nbbc.edu> > > > MySQL cannot even handle sub-queries yet. > > Ohh! Really? > Allow me to pay my highest respect to the genius mySQL > programmers! > I completely have no clue on how to construct any single > tiny database on a DBMS having no sub-query capability. > > Being too dumb, I solicit mySQL programmers' help by showing > me employee FOO's birthday and his/her latest job title > effective on or before 2003-1-1 from the following tables: > > CREATE TABLE t1 (employee TEXT,BirthDay DATE); > CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle > TEXT); > > And make the result like this: > > FOO 1980-1-1 programmer > > Please do not give me the answer that you will merge these > two tables to form one like this: > > CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate > DATE,JobTitle TEXT); I have great trouble following your meaning, but I think you are talking about joining two tables in a query: SELECT t1.employee, t1.birthday, t2.jobtitle FROM t1, t2 WHERE t1.employee = t2.employee; That is not the same as using a sub-query: SELECT employee FROM t1 WHERE birthday > ( SELECT MIN(effectivedate) FROM t2 ); (select employees who were born after the longest-serving employee started work.) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Who shall ascend into the hill of the LORD? or who shall stand in his holy place? He that hath clean hands, and a pure heart..." Psalms 24:3,4 -
Re: PostgreSQL Advocacy, Thoughts and Comments
Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-11-29T14:04:06Z
On Sat, 29 Nov 2003, Oliver Elphick wrote: > On Sat, 2003-11-29 at 04:37, cnliou wrote: > > "Jason Tesser" <JTesser@nbbc.edu> > > > > > MySQL cannot even handle sub-queries yet. > > > > Ohh! Really? > > Allow me to pay my highest respect to the genius mySQL > > programmers! > > I completely have no clue on how to construct any single > > tiny database on a DBMS having no sub-query capability. > > > > Being too dumb, I solicit mySQL programmers' help by showing > > me employee FOO's birthday and his/her latest job title > > effective on or before 2003-1-1 from the following tables: > > > > CREATE TABLE t1 (employee TEXT,BirthDay DATE); > > CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle > > TEXT); > > > > And make the result like this: > > > > FOO 1980-1-1 programmer > > > > Please do not give me the answer that you will merge these > > two tables to form one like this: > > > > CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate > > DATE,JobTitle TEXT); > > I have great trouble following your meaning, but I think you are talking > about joining two tables in a query: > > SELECT t1.employee, t1.birthday, t2.jobtitle > FROM t1, t2 > WHERE t1.employee = t2.employee; > > That is not the same as using a sub-query: > > SELECT employee > FROM t1 > WHERE birthday > ( > SELECT MIN(effectivedate) > FROM t2 > ); > > (select employees who were born after the longest-serving employee > started work.) > I think he means for the employee FOO show only the latest job title. Or in other words: SELECT t1.employee, t1.birthday, t2.jobtitle FROM t1, t2 WHERE t1.employee = t2.employee AND t1.employee = 'FOO' AND t2.effectivedate > CAST('2003-1-1' TO DATE) ORDER BY t2.effectivedate DESC LIMIT 1 which of course uses a PostgreSQL customisation. I've got a feeling it's possible doing self joins and the like but I'll leave it at that I think. -- Nigel -
Re: PostgreSQL Advocacy, Thoughts and Comments
Chris Travers <chris@travelamericas.com> — 2003-11-30T12:59:09Z
Hi; The MySQL manual states that Joins perform better than subselects. The stated reason is that the planner can better optimize a join. I am, however, very skeptical of this, and would generally assume things to be the other way around. How do subselects perform in relation to hash joins, etc? Best Wishes, Chris Travers
-
Re: PostgreSQL Advocacy, Thoughts and Comments
Martijn van Oosterhout <kleptog@svana.org> — 2003-11-30T14:12:29Z
Hehe, it's kinda funny when people say things like that. It depends on what you call a "join". From a certain point of view, an IN expression is a join too, making subselects a slightly unusual join. In some ways I think a subselect is the SQL way way of expressing a type of join that cannot be expressed in the normal way and that with a different query language, the concept may go away entirely. I do know that in recent versions of postgres, certain types of subselects are actually optimised into specific types of joins. It's all just relational algebra people! So the moral is, MySQL needs a better query optimiser? Hope this helps, On Sun, Nov 30, 2003 at 07:59:09PM +0700, Chris Travers wrote: > Hi; > > The MySQL manual states that Joins perform better than subselects. The > stated reason is that the planner can better optimize a join. > > I am, however, very skeptical of this, and would generally assume things to > be the other way around. > > How do subselects perform in relation to hash joins, etc? > > Best Wishes, > Chris Travers > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
-
Re: PostgreSQL Advocacy, Thoughts and Comments
Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-30T18:03:44Z
"Chris Travers" <chris@travelamericas.com> writes: > The MySQL manual states that Joins perform better than subselects. Very possibly true ... in MySQL. Since they have such an immature subselect implementation (not even out of alpha apparently), it'd not be surprising if they can't optimize subselects worth a damn yet. Our planner has been hacked on repeatedly to do a good job with subselects --- and I wouldn't want to imply that the process is done yet. One of the more amusing aspects of watching MySQL's response to the "feature race" is how they invariably gloss over the difference between having a minimal implementation of a feature, and having a feature that is mature, complete, and efficient. Subselects are one example where there's a lot of mileage yet to cover after you get to the point where you can say "it works". regards, tom lane