-1

I have 3 tables: video, video_category and video_category_join.

One video can have multiple video_category and a video_category can be associated with multiple videos: therefore the video_category_join table

Like so:

TABLE: video

| video_id | title | description | duration |
|--------------------------------------------
| 12       | hello | ...         | 190      |
| 24       | hello | ...         | 190      |
| 78       | hello | ...         | 190      |
| 34       | hello | ...         | 190      |
| 91       | hello | ...         | 190      |
| 88       | hello | ...         | 190      |

TABLE: video_category

| video_category_id | name    |
|------------------------------
| 4                 | music   |
| 8                 | tv      |
| 5                 | black   |
| 2                 | politic |
| 1                 | movie   |
| 6                 | other   |

TABLE: video_category_join

| video_id | video_category_id |
|------------------------------
| 24       | 4                 |
| 24       | 5                 |
| 88       | 1                 |
| 91       | 6                 |
| 91       | 5                 |
| 78       | 4                 |

Given a video_id, i want to pick videos that DON'T have the same category as the given video_id.

So, for example, if the video_id is 24, the result set should return videos that dont have the same category as video_id 24. In this case, the only videos that dont have the same category as video_id 24 are: video_id 88.

So, after posting the question on the network i was able to experiment with different queries that give the same result. Because i'm not an expert, i was wondering if there are advantages/disadvantages on using on or the other.

As you can see, one uses HAVING with COUNT and the other uses IS NULL.

SELECT vc.video_id
FROM video_category_join vc
LEFT JOIN video_category_join vc2 ON vc.video_category_id = vc2.video_category_id AND vc2.video_id = 88
WHERE vc2.video_id IS NULL
GROUP BY vc.video_id

vs

SELECT vc.video_id
FROM video_category_join vc
LEFT JOIN video_category_join vc2 ON vc.video_category_id = vc2.video_category_id AND vc2.video_id = 88
GROUP BY vc.video_id
HAVING COUNT(vc2.video_id) = 0;
  • Please clarify. "the same category as" --> "any of the same categories"? or did you want "all of the same categories"? And what if the other video has all of the same categories, plus others? – Rick James Jul 21 at 4:15
2

In an ideal world, and as SQL is a declarative language, the optimizer would be able to realize that both queries are looking for the same thing and provide you with an identical execution plan for both.

In the real, non-ideal world, the answer to your question is 'it depends'. It depends on multiple factors, which are at the discretion of the query optimizer, and may change when data volumes or distribution change, when your MySQL version changes, and with some engines (not sure about MySQL specifically) even when the workload and available hardware resources change, or when a ready-made plan is available in cache from a previous query.

The only way to tell is to check it for yourself. If you attach the EXPLAIN plans for both queries, based on actual production data volumes and value distribution, we can take a look.

My recommendation is not to worry about it, and use the syntax that is most clear and readable. If you happen to encounter a performance challenge with this query in the future, you can check other syntax variants and see if one performs better than the other.

I find the syntax of both queries that you suggested less than ideal. Why not translate your requirement from english to SQL in the following manner:

WITH categories_to_exclude
AS
(
SELECT video_category_id
FROM   video_categories 
WHERE  video_id = 88
)
SELECT v.video_id
FROM   videos AS v
WHERE  NOT EXISTS (
                    SELECT NULL
                    FROM   video_categories AS vc
                    WHERE  vc.video_id = v.video_id
                           AND 
                           vc.video_category_id IN  (
                                                    SELECT video_category_id
                                                    FROM   categories_to_exclude
                                                    )
                  );

Even though it might seem longer (much of it is due to my formatting habits and the use of the CTE), this is a direct translation from english to SQL, and at least in my eyes is self-explanatory:

  1. Start by figuring out which categories need to be excluded and give it a descriptive name using a CTE - categories_to_exclude.
  2. Show me all videos (SELECT video_id FROM videos)
  3. For which there isn't a single category that this video has (WHERE NOT EXISTS ... vs.video_id = v.video_id)
  4. AND Which is also one of the categories we want to exclude (AND ... IN (SELECT ... FROM categories_to_exclude).

There is also a reasonable chance that this syntax will turn out the be the most efficient, as the NOT EXISTS predicate opens up an optimization opportunity for the optimizer not to process the entire date set like in both your suggested queries.

With EXISTS, it is enough that one row comes back from the sub query to make the predicate FALSE, and the optimizer can rule that video out immediately and move on to the next one. That doesn't mean that the optimizer will take advantage of it, but it may.

BTW - the SQL standard provides the EXCEPT set operator which would have made this much cleaner and shorter, but unfortunately MySQL doesn't support it yet.

With engines that do support it, it would be an even simpler more direct translation from english - "Show me all videos except the ones that have a category from the ones we want to exclude":

WITH categories_to_exclude
AS
(
SELECT video_category_id
FROM   video_categories 
WHERE  video_id = 88
)
SELECT v.video_id
FROM   videos AS v
EXCEPT
SELECT vc.video_id
FROM   video_categories AS vc 
WHERE  vc.video_category_id IN  (
                                 SELECT video_category_id
                                 FROM   categories_to_exclude
                                );

HTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.