如何在电影数据库中找到哪些导演没有电影？ [英] How do I find which directors do not have a movie in the movie database?

问题描述

所以我有两张桌子：导演和电影。



导演包含：ID，姓名，dob，性别

电影包含：ID，name，director_id，release_date和release_year（外键） ：director_id）



我必须找出哪些导演在电影表中没有电影，但我不知道该怎么做。请帮忙。

So I have two tables: directors, and movies.

Directors contain: ID, name, dob, gender
Movies contain: ID, name, director_id, release_date, and release_year (foreign key: director_id)

I have to find out which directors do not have a movie in the movies table but I don't know how to do this. please help.

推荐答案

有很多方法可以查询，但我认为 NOT EXISTS 子句是一个考虑以下示例：

There are a lot of ways for querying this but I think NOT EXISTS clause is one of the most verbose.

Consider the following example:

SELECT *
FROM Directors d
WHERE NOT EXISTS (SELECT 1
                  FROM   Movies m
                  WHERE  m.director_id = d.id)

NOT EXISTS子句中的子查询是一个相关子查询。换句话说，逻辑上为Directors表中的每一行运行子查询，并结束数据库检查子查询是否使用director行中的id返回一行。



如需更多解释，请查看

- 子查询基础知识 [ ^ ]

- 相关子查询 [ ^ ]



另一种方法是不使用相关子查询，但事先获取导演ID。在这种情况下，查询可能类似于

The subquery in the NOT EXISTS clause is a correlated subquery. In other words, logically for each row in the Directors table the subquery is run and end the databses checks if the subquery returns a row or not using the id from the directors row.

For more explanations, have a look at
- Subquery Fundamentals[^]
- Correlated Subqueries[^]

Another way would be to not use a correlated subquery but fetch the director id's before hand. In such case the query could look something like

SELECT *
FROM Directors d
WHERE d.Id NOT IN (SELECT DISTINCT m.director_id
                  FROM   Movies m)

在此示例中，首先创建导演ID的列表，并为每部电影创建查询检查导演的ID不在列表中。关键字DISTINCT用于消除重复，因为无论一个电影有多少，每个导演ID都只列出一次就足够了。



有关DISTINCT的更多信息，请参阅< a href =https://msdn.microsoft.com/en-us/library/ms176104.aspx> SELECT子句（Transact-SQL） [ ^ ]

SQL的位性能优化版本会就像

A bit performance optimized version of the SQL would be like

SELECT *
FROM   DIRECTORS D
LEFT OUTER JOIN MOVIES M ON D.DIRECTOR_ID = M.DIRECTOR_ID
WHERE M.DIRECTOR_ID IS NULL

子查询具有性能成本，因为它们会评估每一行。

Sub Queries have performance cost because they evaluate over each row.

这篇关于如何在电影数据库中找到哪些导演没有电影？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！