MySQL:查找不参与关系的行 [英] MySQL: Finding rows that don't take part in a relationship

问题描述

我有两个表:电影"和用户". 两者之间存在n:m关系，描述用户观看过哪些电影.这用表"seen"来描述 现在，我想为给定的用户找出他还没有看过的所有电影. 我当前的解决方案是这样的:

I have two tables: 'movies' and 'users'. There's an n:m relationship between those, describing what movies a user has seen. This is described with a table 'seen' Now i want to find out for a given user, all the movies he has not seen. My current solution is like this:

SELECT *
FROM movies 
WHERE movies.id NOT IN (
     SELECT seen.movie_id 
     FROM seen 
     WHERE seen.user_id=123
)

这很好，但扩展性似乎不太好.有更好的方法吗?

This works fine but does not seem to scale very well. Is there a better approach to this?

推荐答案

这是不使用您显示的子查询方法来执行此查询的一种典型方法.这可能满足@Godeke的要求，以查看基于联接的解决方案.

Here's a typical way to do this query without using the subquery method you showed. This may satisfy @Godeke's request to see a join-based solution.

SELECT * 
FROM movies m
 LEFT OUTER JOIN seen s
 ON (m.id = s.movie_id AND s.user_id = 123)
WHERE s.movie_id IS NULL;

但是，在大多数品牌的数据库中，此解决方案的性能可能比子查询解决方案差.最好使用EXPLAIN来分析这两个查询，以查看在您的模式和数据下哪个查询会更好.

However, in most brands of database this solution can perform worse than the subquery solution. It's best to use EXPLAIN to analyze both queries, to see which one will do better given your schema and data.

这是子查询解决方案的另一个变体:

Here's another variation on the subquery solution:

SELECT * 
FROM movies m
WHERE NOT EXISTS (SELECT * FROM seen s 
                  WHERE s.movie_id = m.id 
                    AND s.user_id=123);

这是一个相关子查询，必须为外部查询的每一行进行评估.通常这很昂贵，并且您的原始示例查询更好.另一方面，在MySQL中，"NOT EXISTS"通常比"column NOT IN (...)"更好

This is a correlated subquery, which must be evaluated for every row of the outer query. Usually this is expensive, and your original example query is better. On the other hand, in MySQL "NOT EXISTS" is often better than "column NOT IN (...)"

同样，您必须测试每个解决方案并比较结果以确保确定. 在不衡量性能的情况下选择任何解决方案都是浪费时间.

Again, you must test each solution and compare the results to be sure. It's a waste of time to choose any solution without measuring performance.

这篇关于MySQL:查找不参与关系的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！