Re: IN or EXISTS?? faster one

On Fri, 20 Dec 2002 04:18:42 -0600, Scott Lamb <slamb@slamb.org>
wrote:
>that's also assuming d.company_name is distinct in depot.

No, it's not.

CREATE TABLE depot (depot_id INT, company_name TEXT);
INSERT INTO depot VALUES (1, 'SOME');
INSERT INTO depot VALUES (2, 'ANY');
INSERT INTO depot VALUES (3, 'SOME');

CREATE TABLE bom (bom_id int, depot_id int);
INSERT INTO bom VALUES (11, 1);
INSERT INTO bom VALUES (12, 2);
INSERT INTO bom VALUES (13, 3);
INSERT INTO bom VALUES (14, 4);
INSERT INTO bom VALUES (21, 1);
INSERT INTO bom VALUES (22, 2);
INSERT INTO bom VALUES (23, 3);

SELECT bom.*
  FROM bom, depot d
 WHERE bom.depot_id = d.depot_id
   AND d.company_name = 'SOME';

SELECT bom.*
  FROM bom INNER JOIN depot d ON bom.depot_id=d.depot_id
 WHERE d.company_name ='SOME';

> Otherwise
>you'll get repeated bom.*s for each d it matches.

No repeatd boms here:
 bom_id | depot_id
--------+----------
     11 |        1
     13 |        3
     21 |        1
     23 |        3
(4 rows)

The point is: there can be many depots matching company_name='SOME',
but there's at most one matching depot for each bom.

Servus
 Manfred