Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this lesson, I'll show you a practical example of using a sub select to provide a list of rows to search for in a table. We're going to be using the album database for this lesson. I'm going to start with a simple query. It'll give us a list of albums that have tracks with a duration of 90 seconds or less. And so for this, I'm going to select from the track table, and I'm going to select distinct album IDs where the duration of a track is less than or equal to 90 seconds.
So it looks like this, SELECT DISTINCT album_id FROM track WHERE duration is less than or equal to 90 seconds. And when I say Go, I get a list of two album_ids. And these are the albums that have tracks with durations of less than or equal to 90 seconds. Now, I can get all of the details on the albums by using this query. As just the list of album IDs for a where clause in a query on the album table.
So, that looks like this. So, now I'm selecting from the album table, and I'm using the result of this other query to provide the IDs for the rows that I want to see. From the album table. So I press Go. And now I have rows from the album table with these IDs. So that's very useful. In fact, I can get a little bit more complicated if I want to. I can create a join query to get the full details of the album and tracks for the entire albums for these two IDs.
So that would look something like this. So A will represent the album table, and T will represent the track table. So there I'm getting title as album, artist from the album table, track number from the track table as sequence, title from the track table, and duration from the track table as seconds. And so I'll have FROM album AS a, and JOIN track AS t. And I'm joining on, t.album_id equals a.id.
So that's all very straightforward. I'm using this same where clause and I'm just going to insert an ORDER BY and when I press Go. Looks like I have a little typo here. This needs to be a.id, and I press Go and I have these two albums and all of their tracks and all of their data from both of these tables. Now, if I want to just get the tracks that I'm looking at, I can actually simplify this query quite a bit.
So, instead of joining the track table, I can simply join this query like this. As t, and I need to add these other columns here. Track number, duration, and title. Because this query is coming from the track table anyway, right? And I still need an on clause for my join, like that. And when I press Go, now I just have these two tracks.
And I'm using this sub select, as the joined table. So, a sub select, as you can see, can be used anywhere you would use a table, because the result of a select is effectively another table itself. In the next lesson I'll show you how you can save your select query as a view. And that view can then be used over and over again. In place of a table in the same way you would use a sub select.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101694 Viewers
61 Video lessons · 88445 Viewers
71 Video lessons · 72283 Viewers
56 Video lessons · 104006 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.
Your file was successfully uploaded.