Sql-server – How to visualize joins between inner queries to their respective outer queries

existsjoin;sql serversql-server-2016subquery

I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.

Here's an example:

SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE EXISTS
(
    SELECT TOP 1
    FROM Ninjas AS N
    INNER JOIN NinjaWeapons AS NW
        ON N.WeaponId = NW.WeaponId
    WHERE N.NinjaId = NM.NinjaId
)

Is there a way to represent this (or a similar kind of inner query joined to outer query) in a table or diagram of sorts?

Best Answer

Just break it down into stages and consider what the output of each stage represents

SELECT *
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
WHERE EXISTS
(
    SELECT *
    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 EXISTS.

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.

Related Question