Select you crazy Query

Lets try a small SQL quiz this time. We have two tables foos and bars with the definition below..

CREATE TABLE foos ( foo_id INT NOT NULL, PRIMARY KEY (foo_id) ) ENGINE=InnoDB;

CREATE TABLE bars ( foo_id INT NOT NULL, bar_id CHAR(1) NOT NULL, PRIMARY KEY (foo_id, bar_id), FOREIGN KEY (foo_id) REFERENCES foos(foo_id)) type=InnoDB;

And lets do some sample data inserts..

INSERT INTO foos VALUES (1), (2), (3), (4);

INSERT INTO bars VALUES (1, 'a'), (2, 'b'), (3, 'b'), (3, 'c');


Now write query to give me all foos and those bars who have a bar_id 'b' for the same foo_id. Seems like an easy OUTER JOIN. This is what I came up with initially..

SELECT * FROM foos LEFT OUTER JOIN bars ON foos.foo_id = bars.foo_id WHERE bar_id = 'b' OR bar_id IS NULL;

And the result set was..

foo_id	foo_id	bar_id
2	2	b
3	3	b
4	NULL	NULL
3 rows in set (0.00 sec)

But the result set obtained is wrong because we did not get all the foos. Get back to the query then to obtain the result set below..

foo_id	foo_id	bar_id
1	NULL	NULL
2	2	b
3	3	b
4	NULL	NULL
4 rows in set (0.00 sec)

For the correct query just make a minor change to the above SELECT

SELECT * FROM foos LEFT OUTER JOIN bars ON foos.foo_id = bars.foo_id AND (bar_id = 'b' OR bar_id IS NULL);

I did not have enough time to search for the actual reason for this behaviour though and am not even sure if this is standard or MySql specific.

In table bars the primary key is a composite key between foo_id and bar_id. When foo_id is compared in the ON clause, it appears as if the rest of the primary key is forgotten. foos (1) joins with something like bars (1, NULL) and so the WHERE clause fails. When the ON contains all the clauses for the whole composite key, the LEFT OUTER JOIN behaves as expected.

That gives a whole new perspective to outer joins when dealing with composite primary keys then.