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-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql