Just break it down into stages and consider what the output of each stage represents
FROM Ninjas AS N
INNER JOIN NinjaWeapons AS NW
ON N.WeaponId = NW.WeaponId
This can be thought of as "all ninjas with at least one weapon along with their weapons". The data model does not appear to tie weapons to particular movies.
So you are now left with the simpler query to analyse (there is no value in including
TOP 1 in an
EXISTS so that has also been removed.)
SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
FROM WeaponOwningNinjasWithTheirWeapons AS N
WHERE N.NinjaId = NM.NinjaId
There seems to be a normalisation issue here as apparently
NinjaMovies will contain multiple rows for the same movie starring more than one Ninja and repeats the title in each one - but perhaps
NinjaMovies is itself a view resulting from a join of normalised tables.
In any event
SELECT NM.MovieId, NM.MovieTitle clearly selects the Movie Ids and Title from it so the only thing left to analyse is the
This simply says to only return rows where the corresponding Ninja exists in the
WeaponOwningNinjasWithTheirWeapons result set.
So to sum up. The query returns all rows in
NinjaMovies where the corresponding Ninja has at least one weapon.
The semantics of the query seem a bit odd as
NM.NinjaId is not projected so in the event that the movie stars multiple ninjas - some with weapons and some without - there is no indication as to which led to the inclusion of the row. If you don't care about this then you should probably add a
DISTINCT to remove duplicates in the event that the movie stars multiple weapon bearing ninjas.