In nested queries we have seen "in", "exists",
there is also another keyword like another operation called "unique".
But also you can say "not in",
"not exist" or "not unique".
There is also "any" and "all",
which is very important when it comes to nested queries.
So here, if you want to find sailors who's rating is
greater than some sailor called Horatio.
So Horatio basically is a sailor and I want to
find any kind of sailors that have a rating larger than this guy.
So in this case, you can select everything like all sailors with name of Horatio.
And then in the upper like in the outside the nested query,
the main queries you say,
on the Select the sailor such that the rating is larger than "any" one named Horatio.
So this will simplify this kind of query.
Again, if you're going to do to join,
it's going to be way more complex than that.
Okay, one example, find the sailor ID's of
sailor who have with reserve both a red and a green boat.
We've seen how to do this using the intersect operation sequel.
But how can we do it using a nested query?
With the "in" specifically, the "in" keyword.
So to do that you want to Select,
you do the join on boats reserves and sailors.
You want to make sure that the sailor,
this is the equijoin condition in the qualification.
The other equijoin condition between the boats and the reserves.
And then color equal red.
Besides that, so again,
you got the color equal red.
So far, you have got this parts to get the green
also like all the sailors returned here needs to
be also in the set up sailors that return
the same inquiry but instead of color equal red it does color equal green.
So this is why you use the "in" that way.
So this way you've got the whole result of the query.
And to find the names not the sid's who have reserved a red and a green,
you have to replace the sid here with the s.sny.
So do it again, in the target list you have to specify what you need.
You can also select the boat ID here,
you can return also all the other operations and you can decide whether Distinct or not.
So this just to give you a little bit of a flavor of how SQL's are
very powerful language and it allows you to do the same exact operation in so many ways.
But the key point here,
is that you don't need to think
about the sequence of operation or all that you need to think is that how
can you declaratively use these different rich of
keywords to return the data that you want.
And then the database system will take that and
figure out the better way to optimize these.
And again, "in" and "exists" and all these nested queries
it would be replaced inside the database usually with a join.