SQL Inner Join for Movie Ratings: Find Movies with 4 or 5 Stars

This SQL query uses an INNER JOIN to find movies that received a rating of 4 or 5 stars, sorted in increasing order of rating.

SQL Query:

SELECT year, ratingStars
FROM Movie
INNER JOIN Rating ON Movie.mID = Rating.mID
WHERE ratingStars >= 4
ORDER BY ratingStars ASC;

Explanation:

Inner Join: The INNER JOIN combines the Movie and Rating tables based on the shared mID (movie ID) column. This ensures that only movie records with matching ratings are included in the result set.
Filtering: The WHERE clause filters for movies that received a rating of 4 or 5 stars (ratingStars >= 4).
Sorting: The ORDER BY clause sorts the result in ascending order of ratingStars to present the highest rated movies first.

Why Use Inner Join?

The INNER JOIN is crucial for combining data from different tables based on a common field. In this case, it allows us to retrieve both movie information (from the Movie table) and its rating (from the Rating table) in a single query. Without it, we would have to perform separate queries for each table and then manually combine the results. The INNER JOIN simplifies this process and ensures data consistency by only including records that exist in both tables.