Thread: 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
On 6 June 2010 06:30, Nilesh Govindarajan <lists@itech7.com> 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.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
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-owner@postgresql.org [mailto:pgsql-sql-owner@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
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