Video Transcription

Welcome back everyone. In this video series, we’re gonna be taking a look at table expressions. So previously, we talked about sub queries, particularly two types of sub queries self contained and correlated, the self contained sub queries being a sub query that can actually execute in isolation, so on its own without any dependency with the outside query. correlated query is the opposite of that, where we are referencing at least one attribute or column, and the inner query from the outer query. So we also talked about three different types of return values for sub queries that being single valued and multi valued. So those are the two primary ones that we covered. We also briefly talked about table valued returns. So remember, the single valued query is just as it sounds right, one single value that’s actually returned, so like a number or a string of some kind. And then multivalued sub queries are ones who are going to return one single column, but more typically more than one row. So technically, a single valued return is also a multivalued return. But a multivalued return can have more than one value, right? More than one row. But what if we have more than one column. So we really didn’t talk too much about what happens when a sub query returns more than one column. But that’s going to be our focus for today. So a query that or a sub query that returns more than one column is going to be commonly referred to as a table expression.

A table expression is a named query expression that represents some form of table. And now this table may not be physical in the database more often than not, this is actually going to be a logical construct only. So meaning that it’s a collection of information that has been formed into a table using the query rather than a an existing physical table in the database. Along with that, right, the table expression itself is merely just a reference. So anytime this table expression is actually referenced, is going to be a collection of queries that executes to create that table as a result. So why why the table expression? Well, generally speaking, table expression is not going to actually offer you any performance increases. So your SQL commands are not going to actually run any faster, for the most parts and using table expressions. But logically, they become usually easier to read. So this is very similar to the idea of functions and programming, right? We often use functions in programming to reduce the amount of duplicated code, or code that’s been copy and pasted or repeated, similar kind of idea with the table expression, since we can actually have a named representation of that table expression. That’s typically a complex piece of complex piece of SQL. So more or less, just logical benefits here, so readability and then also reuse depending on the type kind of table expression.

Some of our table expressions that will actually cover here are not allowed to be reused more than once. So that does come with a caveat there. And like I said, very rarely is a table expression used to increase the efficiency or productivity of your SQL code. There are some edge cases depending on the scenario, but more often than not, it’s used primarily for logical readability and construct, the easier construction of your SQL. Other things that include a table expression. So for a table expression to be considered a valid table expression, we have a few other things to consider. So first off, very similar to how we work with things like joins and stuff like that order is not guaranteed unless it’s actually ordered. Now, there is a tricky part with that as well as we’re not actually allowed to use an order by in certain scenarios and will actually show an example of this here in a few minutes. But all columns that exist inside of this table expression must be named. So we can’t have an unnamed column as parts. So Like if we do an aggregate like say average or count or something like that, we can’t just leave count star there, we actually have to say count star as or apply an alias to that column. Unlike a normal query where we don’t have to necessarily name our columns. If we’re not referencing them later.

Similar to an actual table, all column names must be unique. Okay, all column names must be unique. So think of a lot of the qualities of a general physical SQL table to be similar to the logical table expression as a result. Now, our table expression can be used in a lot of different scenarios. Pretty much in any data manipulation statements that we actually write, we can use a derive table there. And we’ll show a variety of different examples of this in action. But usually, the table expression is going to exist, for the most part inside of the from clause. But we’ll show except we’ll show a couple different examples of where we can place this inside of our queries. But different there are four primary types of table expressions that we’ll cover in the this video series. The first one are derived tables, which are most closely related to a sub query. So all of the sub queries that we covered previously, we only covered remember the single value queries and multivalued queries, but we didn’t get to the table value queries. And so that’s what a derive table is going to be. Then we have common table expressions, which are starting to look a little bit like functions similar to views and inline table valued functions. So an inline table valued function is like a view but with a couple parameters allowed with it. But each of these will be a topic in the upcoming videos.