Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A subselect is effectively a nested select statement. In SQL, the result of a select statement is effectively a table. It usually just exists in memory but it can always be used, as you would use a table. Because of this, a select statement may be used as a data source for another select statement. This usage is called a subselect or sometimes a subquery. For this lesson we'll be using the World Database. And I'm going to copy and paste some code from the chapter eight exercise file. And I'm just going to grab this much of it, down to this first select statement there.
Copy and paste it into the SID. Go ahead and press go. See, I create a temporary table called T. It just has two columns, A and B. And there are just six character strings. And I've inserted three rows. In column A we have state codes, followed by numbers. And in column B we have country codes, followed by numbers. And I have this select statement using substrings to break apart these pact fields so effectively in column A we have two different pieces of information.
We have the state code. And some sort of a nonsense code there, and in column B we have the country code and the four letter code there. And that's all happening from the select statement with sub string functions. So if I come back out here to the exercise files, I'm going to grab this select statement here and. Paste this in, and you'll see that we're using exactly this same select statement with those substrings, but we're using it as a data source. So, everything from this opening parenthesis to this closed parenthesis is just the results of that select.
And you see I have alias, an AS ss. And so I'm grabbing ss CCode, and you can see that that's this CCode here, which is this substring, it's these four digits here in this fourth column of the result of that select. And so effectively, this here is the Subselect. So, this is the result of the select that we ran before. And you see it has four columns because we're using substring and AS, and substring and AS.
And so those columns can be used just as if they were a table in a database. And so that is one source. Is this select statement, and it's joined with the country table. And it's keyed on this s s country. S s country is this two letter code, right there in that third column. And that's keying on the country table code two, which is the two letter country code, so that we can actually find the name from the country table, right.
And when I press go. We now have name and we have CCode, that's these two columns here from our joint query. So this is very, very handy. You can do some processing in a select statement and use that as a data source for another query. So subselects are a convenient way of making your data available in different forms while keeping your database schema simple and well organized. So, I'm going to go ahead and drop this temporary table to put our database back in its original state for the rest of the course.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 100173 Viewers
56 Video lessons · 113180 Viewers
71 Video lessons · 82048 Viewers
131 Video lessons · 39370 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.