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.
source to share
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.
source to share