How do I find which directors do not have a movie in the movie database?

Question

1.00/5 (1 vote)

See more:

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.

Posted 14-Jan-16 17:11pm

Member 12265266

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-01-14T18:42:00

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:

SQL

SELECT *
FROM Directors d
WHERE NOT EXISTS (SELECT 1
                  FROM   Movies m
                  WHERE  m.director_id = d.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

SQL

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

In this example the list of director id's is created first and for each movie the query checks that the id of a director is not in the list. The keyword DISTINCT is used to eliminate duplicates since it's sufficient that each director id is listed only once regardless of how many movies one has.

For more information about DISTINCT see SELECT Clause (Transact-SQL)[^]

_Asif_ · Answer 2 · 2016-01-14T20:46:00

Solution 2

A bit performance optimized version of the SQL would be like

SQL

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.

Posted 14-Jan-16 20:46pm

_Asif_