For those who don’t use SQL too much, just like me.
I’ve been using MySQL more than what I would like to. In these endeavors, I’ve always felt the need for one thing: Negated joins. For example, in a many-to-many, I want to know what categories a post is not linked to.
For those who had similar problems, I present you two solutions:
- MySQL 5.0 Reference Manual :: 12.2.8.6. EXISTS and NOT EXISTS
- Doing INTERSECT and MINUS in MySQL: classical use-left-join-to-find-what-isn’t-in-the-other-table
Thank you so very much!
February 17th, 2009 at 11:24 pm
select * from category
where category.id not in (select cat_id from cat_post where post_id = $the_post_id)
This way seems easier and it’s probably faster as DB usually don’t cope well with ‘exists’ clauses.
February 18th, 2009 at 12:24 am
Hmm, that’s contradictory to my MySQL experience. How/where did you observe that behaviour?
February 18th, 2009 at 12:56 am
Imagine this query from the link you gave above:
SELECT DISTINCT store_type FROM stores
WHERE NOT EXISTS (SELECT * FROM cities_stores
WHERE cities_stores.store_type = stores.store_type);
For each line of the outer query the inner query has to be computed again because stores.store_type changes. Of course MySQL can have an intelligent caching mechanism, but if the number of stores is very big it won’t be enough. If MySQL is really intelligent it will transform the query into something similar to what I proposed. I know for a fact that PostgreSQL doesn’t like this type of queries very much.
In the example I wrote before. The inner query has to be performed only once and then it’s just a matter of using the index wisely.
Have you tried both approaches with some big chunks of data? You won’t notice the difference with small tables.
IMHO I think that the NOT IN clause is easier to understand than the NOT EXISTS clause and also more elegant. But it’s a matter of taste really …
February 19th, 2009 at 12:07 am
Hmm. I’ll run some benchmarks and post them.
About the style, it varies. NOT IN is sometimes more legible, sometimes it’s NOT EXISTS.