Video Transcription

Welcome back everyone. In this video we’re going to be focusing on sub queries. Now up to this point, we’ve covered how to do single table queries. Multi table queries with join. So Inner Joins cross joins outer joins. But in this video, we’re going to be focusing on how we can nest queries inside of each other. But before that, let’s review a few things. Remember our processing order of SQL and the order that we write SQL is different. So the way we have to write SQL uses order of statements is listed here. But they’re processed, starting from the from clause, because before we can actually enter any of the other phases, we have to know what data to start with. Where allows us to filter those filter that data row by row, then we can of course, group that data together, and then filter those groups. So we have a the where clause being a row by row filter, the having clause being grouped by group filter, and then we can finally pick out which columns that we are projecting out and into our results, we can specify that we only want distinct rows from the from the result and also remember, distinct can also be utilized as part of aggregate functions as well, like Distinct Count, or count distinct sorry, then we can order our results as well as sending or descending order, ascending by default. And then finally, we can use top to restrict the number of rows that’s actually returned. So let’s say top five or top 5%. Offset fetch is the SQL standard of top remember, top is only native to Microsoft SQL Server. Fetch and offset are ancy standard and will be present in most SQL languages that you encounter.

So remember that joins are table based operations, we can take two tables as input and produce one table as output. That output table can then be utilized as part of a multi join query. So if we want to join more than two tables together, we can feed the results of one join into a another. Another join, we covered three types of joins. So far, cross joins, Inner Joins, and outer joins. All of these differ a little bit in their processing phases. But remember that the inner join builds off of the cross join and the OUTER JOIN built off of the inner join. So remember, cross joins are going to only have one single face and the base join. The Cartesian product is what is produced as a result of the cross join. So a row each of the rows and table a paired with each of the rows with Table B, all the different combinations there, then the inner join builds off of that cross join. So we have the Cartesian product again. But then we add a filtering step, just like if we had a where clause with it. And then our outer join is a Outer Joins build off of both of them. So we have a cross join first, and then an inner join. So we cross join, filter. And then we have a preservation step, where rows that were not matched, and the filtering phase will be added back in and depending on if it’s a left join, right join or full join designates which table is going to be the preserved table or both. But let’s focus on this videos topic sub queries or nested queries, you can kind of think of a sub query as if it was a nested loop in a sense, so you have an outer loop and an inner loop. So with that, we have an outer query and an inner query.

So the inner query is going to be embedded inside of the outer query or the caller. So the inner queries results will be returned the outer one, and we have a couple variations of this as well. So how this actually functions is going to be logically different depending on the types of values that are returned. And whether or not the inner query is dependent or independent. What that we do have two types or two variations of sub sub queries, self contained, where there is no dependency on the outer query. So the self contained query can execute all on its own without any information from the outer query, a correlated query which has a dependency on the outer query. So this is if we have an inner query or a sub query that depends on a column that is fed in from the outer query. So a correlated sub query cannot be executed on its own. But with each of these we have generally three types of returns a single value multivalued or table value. So single value just as it sounds, it’s a single value. A multivalued is a single column but zero or more rows, so it can return no rows, but it can also return more than one. And then we also have a table valued sub query, which can return one or more columns and zero or more rows within that, but and the following videos we’ll take a look at a bunch of different examples of self contained and correlated sub queries.