SQL 空值和连接，其他表上不存在

Question

我有这两个表

+-------------------+   +----------------------------------------------+   
|      movies       |   |                   ratings                    |  
+-------------------+   +----------------------------------------------+   
| Id   |    Name    |   | Id_movie   | id_user | id_rating | user_rate |
|-------------------|   |----------------------------------------------| 
| 1    |  movie 1   |   | 1          |   20    |   1       |     5     |
| 2    |  movie 2   |   | 1          |   21    |   2       |     3     |
| 3    |  movie 3   |   | 1          |   22    |   3       |     4     |
+-------------------+   | 2          |   21    |   3       |     5     |
| 2          |   22    |   3       |     4     |
| 3          |   22    |   3       |     5     |
+----------------------------------------------+

我想要

+----------------------------------------+   
|                movies                  | 
+----------------------------------------+  
| Id_user   |    id_movie    |  Name     |
|----------------------------------------| 
| 20        |        2       |  movie 2  |
| 20        |        3       |  movie 3  |
+----------------------------------------+ 

用户＝20未对电影2&3.有可能吗？

有人能帮我吗？谢谢

Answer 1

您必须生成所有组合的用户电影，然后测试每个组合是否存在评级。

SELECT user.user id_user, 
movies.id id_movie, 
movies.name
FROM ( SELECT 20 AS user ) AS user    -- needed user(s)
-- for all users use 
-- ( SELECT DISTINCT user FROM ratings ) AS user                                   
CROSS JOIN movies                     -- cross join generates all combinations
WHERE NOT EXISTS ( SELECT NULL        -- select only non-existent combinations
FROM ratings
WHERE ratings.id_user = user.user
AND ratings.id_movie = movies.id )