Re: [SQL] Difference between these two queries ?
On 6 June 2010 06:30, Nilesh Govindarajan wrote: > 1. SELECT b.* from banners b, banners_users bu where b.id = bu.bid and > bu.uid = 5; > > 2. SELECT b.* from banners b INNER JOIN banners_users bu ON b.id = > bu.bid AND bu.uid = 5; Here is an explanation: http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html#QUERIES-FROM > > What is the first type of join called ? CROSS JOIN > > and is it possible that they have different execution times ? AFAIK planner would choose the same execution plan in your situation so it is not. -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] inner join and limit
Hi, Some ways to do that: http://www.sql-ex.ru/help/select16.php > Hi list, > I have two table that are so represented: > t1: > id int primary key > ... other > t2: > id int primary key > t1id int fk(t1.id) > somedate date > ... other > data t1: > 1 | abcde > 2 | fghi > data t2: > 1 | 1 | 2010-05-23 > 2 | 1 | 2010-05-24 > 3 | 1 | 2010-05-25 > 4 | 2 | 2010-05-22 > 5 | 2 | 2010-05-26 > I'm trying to create a query where the data replied are: > join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with date > order (of t2). > Data should be: > t1.id | t2.id | t2,somedate > 1 | 3 | 2010-05-25 > 2 | 5 | 2010-05-26 > As said, I'm trying, but without success... > Can be done for you? > Thanks, > Michele > Здесь спама нет http://mail.yandex.ru/nospam/sign -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Difference between these two queries ?
Nilesh, They should generate equivalent results, But the difference is the constraint on bu.bid=5. In the 1st case it's being done after the join. In the 2nd case it is being done before the join. The end result should be the same, but the execution time can be hugely different. Suppose b has 1b rows, and bu has 50m with a 20:1 cardinality. But bu.bid=5 only select 1 row from bu. If the constaint is applied after the tables are joined the db needs to materialize the entire 1bx50m row set and then Select out the bid=5 rows. Doug -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Nilesh Govindarajan Sent: Saturday, June 05, 2010 9:31 PM To: PostgreSQL SQL; PostgreSQL General Subject: [SQL] Difference between these two queries ? Hi, I have a doubt about JOINS. What is the difference between: 1. SELECT b.* from banners b, banners_users bu where b.id = bu.bid and bu.uid = 5; 2. SELECT b.* from banners b INNER JOIN banners_users bu ON b.id = bu.bid AND bu.uid = 5; What is the first type of join called ? and is it possible that they have different execution times ? -- Nilesh Govindarajan Facebook: nilesh.gr Twitter: nileshgr Website: www.itech7.com Cheap and Reliable VPS Hosting: http://j.mp/arHk5e -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Difference between these two queries ?
Thank you all for your explaination. -- Nilesh Govindarajan Facebook: nilesh.gr Twitter: nileshgr Website: www.itech7.com Cheap and Reliable VPS Hosting: http://j.mp/arHk5e -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
