Re: Select you crazy Query

Are you saying the first query is wrong cause it did not get the results you expected? Cause I don't see anything technically wrong with it. A left outer join does not mean that at the end of the query (whatever it may be), you will get all foos. What you get in the end depends on your WHERE clause.

Break the first query down and you will see how it was arrived at. The cross product of the tables based on the ON clause of the LEFT OUTER JOIN "did" contain foo(1) but the WHERE clause which was applied over the cross product eliminated that record. Hence the result.

The behavior is standard and not MySQL specific. Both those queries on a different engine should return the same behavior. Also, I don't see why the ON clause should remember the composite primary key.

More on left outer join here.

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.

[TrackBack]StartUps in India

Santosh on StartUps in India