Joining views - SQL Tutorial

- Now, as I first mentioned when we first got into the introduction of views, the whole point of creating a view is to save us time, particularly when we are dealing with more complex queries. Now, if you recall on our chapter on subqueries, we introduced the invoice line table and the tracks table together to find out which songs from our tracks table had never been ordered. Now, this is a perfect scenario where it would've been helpful to have a join that linked these two tables together. Then we could have simply created a view out of this sort of complex query. What I've done in front of us here is created that join that is actually connecting our invoice line to our track table, and the result here we have a pretty useful view of what tracks, what prices, and what invoices all go together. Pretty useful but slightly involved when you consider the join that has to go into creating this query. Now this is a perfect candidate to start creating a view out of. Now let's go ahead and do that. To do this, we're just going to push our original query down a bit, and as we did before, we're going to do a create and then keyword view. Then we want to create a useful name for this, so this is actually joining the tracks to the invoices, so we have a deeper view of what's going on with regard to these two data sets, so let's appropriately call this, starting with our best practice capitalized v, then an underscore, then a descriptive name, so tracks, and we'll call this invoice line. So this name clues us into what's going on within the view, and last but not least, we include the "as" keyword, and by doing this, we're now ready to execute this query and now create a view out of this relatively complex SQL statement. Let's go ahead and do that. Now we look for the message that we want to see, which is that this query was executed successfully without errors. We go over to our database structure to double check, and there we have our newly created view: V tracks invoice line. We could go over to our browse tab, as well, and check out our newly created view, as well, and here we have our V tracks invoice line, we have the actual contents of this view, and we have successfully created a new and useful view, particularly one that is relatively complex, the perfect use case for a view.