pko.ch

Reflections about reflection

«
»

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:

Thank you so very much!

This entry was posted on Tuesday, June 17th, 2008 at 8:45 pm and is filed under English, troubleshooting. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

4 Responses to “For those who don’t use SQL too much, just like me.”

  1. André Restivo Says:
    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.

  2. pkoch Says:
    February 18th, 2009 at 12:24 am

    Hmm, that’s contradictory to my MySQL experience. How/where did you observe that behaviour?

  3. André Restivo Says:
    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 …

  4. pkoch Says:
    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.

Leave a Reply