Re: IN or EXISTS?? faster one

On Fri, 20 Dec 2002 04:18:42 -0600, Scott Lamb <slamb(at)slamb(dot)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