[plug] OK, for all of you SQL gurus out there...

[Craig Ringer]

On Thu, May 26, 2005 at 10:57:47AM +0800, Dean Holland wrote:
> I believe your first query, Craig, is the same as simon's. My 
> understanding is equating columns between tables is an INNER JOIN, it's 
> just a much simpler way of writing it. :)

Seems at least on PostgreSQL they are optimized down to the same
query plan:

craig=# EXPLAIN ANALYZE SELECT t1.objectId
FROM thetable t1, thetable t2
WHERE t1.objectId = t2.objectId
AND t1.fieldId = 'bar'
AND t1.value = '1'
AND t2.fieldId = 'foo'
AND t2.value = 'Some text';

 Nested Loop  (cost=0.00..50.01 rows=1 width=4) (actual time=0.050..0.108 rows=1 loops=1)
   Join Filter: ("outer".objectid = "inner".objectid)
   ->  Seq Scan on thetable t1  (cost=0.00..25.00 rows=1 width=4) (actual time=0.027..0.037 rows=2 loops=1)
         Filter: ((fieldid = 'bar'::bpchar) AND ((value)::text = '1'::text))
   ->  Seq Scan on thetable t2  (cost=0.00..25.00 rows=1 width=4) (actual time=0.007..0.019 rows=2 loops=2)
         Filter: ((fieldid = 'foo'::bpchar) AND ((value)::text = 'Some text'::text))
 Total runtime: 0.161 ms
(7 rows)

craig=# EXPLAIN ANALYZE SELECT t1.objectId
FROM thetable t1 INNER JOIN thetable t2 USING(objectId)
WHERE t1.fieldId = 'bar'
AND t1.value = '1'
AND t2.fieldId = 'foo'
AND t2.value = 'Some text';

 Nested Loop  (cost=0.00..50.01 rows=1 width=4) (actual time=0.050..0.109 rows=1 loops=1)
   Join Filter: ("outer".objectid = "inner".objectid)
   ->  Seq Scan on thetable t1  (cost=0.00..25.00 rows=1 width=4) (actual time=0.028..0.038 rows=2 loops=1)
         Filter: ((fieldid = 'bar'::bpchar) AND ((value)::text = '1'::text))
   ->  Seq Scan on thetable t2  (cost=0.00..25.00 rows=1 width=4) (actual time=0.008..0.021 rows=2 loops=2)
         Filter: ((fieldid = 'foo'::bpchar) AND ((value)::text = 'Some text'::text))
 Total runtime: 0.162 ms
(7 rows)

so, at least for a simple query under PostgreSQL they work out exactly the
same.

-- 
Craig Ringer