DISTINCT clause subquery - SQL Tutorial

- [Instructor] As we've seen from other examples that we've done so far in this chapter, sub queries are very helpful, for scenarios where you want to view or compare a query by a condition that requires its own query to calculate. As we learned prior, there is usually one unique field in every table, known as the primary key, which contains a unique number for every record. However, the other fields in a table may contain redundant information. To work better with redundant information, it's often convenient to filter this data, so that it only displays unique or distinct values. Now this is where the distinct keyword comes in. To demonstrate sub queries and the distinct keyword, let's take a look at the invoice line table. The invoice line table shows us which individual WSDA music tracks were purchased on each invoice. Now, let's take a look at this query in front of us here, which is selecting just the track ids from our invoice line table. When we run this query and we take a look at some of our results, for instance track number two, we do see that this track is duplicated. Now if we take a look a little lower down, we also see that this happens with other tracks, like track number eight. And if we take a close look here, we don't see any value for track number seven. We see it goes from six through eight, so we can infer that no one in our record has purchased track number seven. Now our WSDA Music Management team is interested in discovering which tracks are not selling. Now to do retake, to respond to this request, we would need to find a table that links the track id with the invoice id. Now we can use a sub query to list all the tracks by composer and name, that don't appear in the invoice line table. Now, let's include our distinct keyword and modify our existing query here, to see the impact that it has on our result. Now I'm going to just include the distinct keyword after the select, and let's take a look at the result. Before running this query, let's look at our current record count, which is 2,240 records. And again, as we recall, we did spot check a couple records, and saw that we do have duplicate numbers appearing for them. Now, let's run this query. Our updated query with the distinct keyword has shrunk our record countdown to 1,984 rows. And if we take a look at record number two and record number eight, they're now both appearing just one time. We don't see record number seven still. Okay, so now we need a query that's going to list all tracks from our track table, that are not in this particular list. So, let's go ahead and modify our query to do this. What we are actually doing here is needing to first create an inner query from our existing query here, and let's do that by wrapping it in open and closed parentheses. With that done, we could now go ahead and start building out our outer query. So let's say from on this time, we want the tracks table, and let's include our where clause. And we want to say where track id. And this time, instead of saying in, we want the tracks that are not selling meaning the tracks that do not belong to this list. So we're going to say not in the result of our inner query. Okay, now let's complete the build out of our outer query and include the select. And we want to say, select the track id. Let's also select the composer. And last but not least, let's include the name. Okay, now let's look at our full query here. And what we've done is, as we've done before, but this time we're now including not in this particular result. As we saw, we still have our result, which is a distinct list of all of the tracks that we have in our database. Now we're simply responding to WSDA music's latest managerial request, which is to say, which tracks are not selling? So, by composing our query like this, by saying not in this list, we'll get the lists of tracks that are not selling. Let's go ahead and run our query. Now, our query has produced a list. And, as we can see, we have gone to 1,519 rows. And if we take a look at the first record in our list, we do see that record number seven, track ID seven that is appearing in this list, as it was not in our previous list. And this is a sign that confirms to us that we are indeed displaying a list of tracks that are not selling. This is exactly the request that has come from WSDA Music Management. And now we can supply this list to them, whereby they can now in turn make some informed, data-driven decisions.