Re: [sqlite] Increasing performance of joins with a group by clause?

[John Machin]

On 19/08/2007 4:01 AM, Jef Driesen wrote:
Suppose I have two related tables:
CREATE TABLE events (
     id INTEGER PRIMARY KEY NOT NULL,
     place_id INTEGER
);

CREATE TABLE places (
     id INTEGER PRIMARY KEY NOT NULL,
     name TEXT
);

INSERT INTO places (id, name) VALUES (1, 'Place A');
INSERT INTO places (id, name) VALUES (2, 'Place B');
INSERT INTO places (id, name) VALUES (3, 'Place C');

INSERT INTO events (id, place_id) VALUES (1, 1);
INSERT INTO events (id, place_id) VALUES (2, 2);
INSERT INTO events (id, place_id) VALUES (3, 1);

Now, I want to count the number of 'events' at every 'place'. I started
with a simple join and a group by clause to perform the count:

SELECT name, count (*) AS count
FROM events LEFT JOIN places ON places.id = events.place_id
GROUP BY events.place_id;

name|count
Place A|2
Place B|1

It executes very fast (because the join can take advantage of the
primary key) but does not produce the desired result. As you can see,
not all places are included in the result:

So I changed swapped the tables in the join:

SELECT name, count (events.place_id) AS count
FROM places LEFT JOIN events ON events.place_id = places.id
GROUP BY places.id;

name|count
Place A|2
Place B|1
Place C|1

Folks are dumb where I come from; can someone please explain how this 
could be correct? The INSERT statements create 2 events at place_id 1 
(A), 1 event at place_id  2 (B) and *ZERO* events at place_id 3 (C).

Please pardon me if this is strange behaviour (I'm new to this mailing 
list) but I actually *ran* the OP's code, with this result:

C:\junk>sqlite3 junk.sq3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .read outer_join_query.sql
--- query 1 ---
Place A|2
Place B|1
--- query 2 ---
Place A|2
Place B|1
Place C|0
--- query 3 ---
Place A|2
Place B|1
Place C|0
sqlite>


Cheers,
John

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------