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
On Sun, Jun 06, 2010 at 10:15:52AM -0500, Little, Douglas wrote:
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.
In PG version 8.1 (?) and onwards, the query planner knows that they are
equivalent and will choose from the same types of plans. I.e. it's just
a syntax issue, do whichever you think is "prettier", the semantics are
the same.
--
Sam http://samason.me.uk/
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