SQL Difference between two delete queries

I don't understand the difference between the two requests.

First of all, this is the scheme:

 Movie ( mID, title, year, director )
English: There is a movie with ID number mID, a title, a release year, and a director.

Reviewer ( rID, name )
English: The reviewer with ID number rID has a certain name.

Rating ( rID, mID, stars, ratingDate )
English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate. 

      

What is asked:

 Remove all ratings where the movie year is before 1970 or after 2000, and the rating is fewer than 4 stars. 

      

Two queries:

DELETE FROM Rating
WHERE mID in 
            (SELECT m.mID
             FROM Movie m JOIN Rating r ON m.mID = r.mID
             WHERE (year < 1970 or year > 2000) and stars < 4)

DELETE FROM Rating
WHERE mID in 
            (SELECT mID
             FROM Movie
             WHERE year < 1970 or year > 2000)
and stars < 4

      

Now the first request is not correct during the second request, but I don't understand why.

In the first query, I am generating mID

from all films whose rating is below 4 stars and their year before 1970 or after 2000. Then look in the ratings for that mID

and remove the line. (at least what I think it does)

In the second query, I remove from the rating table all those rows where the stars are less than 4, and mID

- this is a movie whose year is before 1970 or after 2000.

+3


source to share


1 answer


DELETE FROM Rating
WHERE mID in 
            (SELECT m.mID
             FROM Movie m JOIN Rating r ON m.mID = r.mID
             WHERE (year < 1970 or year > 2000) and stars < 4)

      

In this request, you are deleting all reviews whose mID matches review IDs with a rating of 4 stars or less, which means that you even remove reviews with a 4 or 5 star rating for films released before 1970 or after 2000. at least one review with less than 4 stars rating. The only check to remove a review is mID, not rating.



DELETE FROM Rating
WHERE mID in 
            (SELECT mID
             FROM Movie
             WHERE year < 1970 or year > 2000)
and stars < 4

      

In this request, you check both conditions (movie release year and rating) mentioned for review deletion.

+4


source







All Articles