From the course: MySQL Advanced Topics (2019)

Creating a subselect - MySQL Tutorial

From the course: MySQL Advanced Topics (2019)

Start my 1-month free trial Buy for my team

Creating a subselect

“

- [Narrator] A sub select is effectively a nested query. In SQL, the result of a select statement may be used as you would use a table. This means that a select statement may be used as a data source in another select statement. This usage is called a sub select. Here we have some code that I've copied from the chapter two exercise file. You'll notice we use the world database, create a table with a couple of columns, insert some data into those columns, and select from it. So when I execute all of this, you see we get this result with two columns, A and B. The A column has, you'll notice, state codes in the first two character positions. And the B column has country codes in the first two character positions. So I can separate these out using sub strings. I'm going to go ahead and copy and paste this statement from the exercise file. And you notice that we have a sub string for the first two positions, and another one starting in column three, or in position three, for the rest of the string. And we have that for both A and B columns. We call them state and country, and S code and C code. And when I execute this statement, you see we get state and country codes, and then these little numeric codes associated with them all separated out with the sub strings. Now where this becomes interesting is when I use this as a data source. So I can create another select statement that uses this as a data source like this, I say select and ss dot C code from, and then put all of this in parentheses, and say AS ss. We got to get rid of this semicolon here. There we go. So just as if this select statement was another data source or another table, add AS ss so that's the sub select so I can get the C code value from that, which we'll notice is this numeric part of column A. And so when I execute this, we get just the C code column, right? So that's using this as a sub select, and I could if I wanted to, I could get the two letter country code. I could say ss dot country comma. And just because that's another column in this data source, we get it out here in our result. I can even use this as one side of a join. I can come down here, and I can say join country, from our country table in the world database. AS co on, co dot code two equals ss dot country, right? And then up here I can say co dot name, and that'll be the name of the country. And now when I execute all of this, I have the name of the country, France, United Kingdom, United States, and their country codes. So that seems pretty useful, doesn't it? We can now drop this table to return the database to its original state. Sub selects are a convenient way of making your data available in different forms while keeping your database scheme as simple and well organized.

Contents