Welcome back. So in our last lesson, we started this discussion about subqueries and we're going to continue that discussion here by discussing a few notes on best practices with subqueries. After this particular video you should be able to discuss how to write a subquery within a subquery within a subquery. You get the idea? Discuss some performance limitations with the overuse of subqueries. Explain how to use subqueriess as calculation. Describe some great general best practices with using subqueries. Okay, to begin with one of the things you'll note is that there's no limit to the number of subqueries you can have in a statement. You can have a query inside a query, inside a query, inside a query. And just keep querying until your heart's content. However, you will start to notice that performance in obtaining your results does slow down as you get deeply nested and write long query statements with a bunch of subqueries. One thing to note is that subquery selects can only retrieve a single column. You can't say, hey, go select all these columns from this table and then bring them in my overall query. It's only going to select a single column at a time. Again, this is why they´re used in filtering a lot. As you can see, this is just an example here of subqueries and a subquery. We have three different queries here. This is one is nicely and dented, so it's a little bit easier to read. Which is a good practice, because if everything was just lined up, you could get lost very easily. And it's hard to see where you at and where are things are coming from and what's happening. One other things that's really important when you're working with subqueries, is to make sure you're properly indenting things. This is going to really help make it easy for you and others to read. Here is an example you can see when I removed it from the indention. And it's really hard to see what we're doing here, where things are starting and where things are ending. So if you're writing some queries, make sure that each SELECT statement is indenting and starting on its own. A helpful website for this is PoorSQL. If you're getting something from an application that generates SQL and they're just really difficult to read because they aren't automatically indented, I always just throw it into this website. And it pre-formats it, indenting things properly so I can easily interpret it. It helps makes it a lot easier to read. Then if you're writing them, just be really clear and consistent in your indenting with your queries. This will make it a lot easier for not only you, but for someone else to read. When you or they are troubleshooting a particularly complex query. Okay, just one more example of subqueries. Subqueries can also be used as calculations. In this example, we want to get the company name and the region, and we want a total number of orders for these customers. Before writing the subquery, what we could do is we could count the number of orders for a particular customer ID, but then we still wouldn't have the customer name and region information. Instead of doing something where we're trying to combine these two together, we're just going to write a subquery. And instead of using the subquery with the WHERE clause, this is actually one of the columns we're selecting. As you can see, we're selecting company name, we're selecting the region and then just as if we were going to select a different column name. We have a whole subquery in there. We have SELECT count as orders from orders, and then what we're saying is we want to count these based on the customer IDs. It's aggregating the orders based on the customer IDs. This is seen where the order customer_id = the customers, customer id. This is coming from the orders table and we're matching it up to our where query through the from of the customers table and the customer ID. Then we just close our subquery in brackets and finish our select statement. We're getting this from customers and we just want order by the company name. Now you can see we have our customer's company name, we have the region they're from and then we have the total orders for that. In this example, you can see it just treats it like another additional column. But really helpful when you're wanting to do some calculations and do a calculated fill on another table. Okay, so to sum up, remember some queries are very powerful. But consider their use very carefully, because it may not always be the best thing to use for performance reasons. There's a lot of other ways we can get the result and not have to use subqueries. For example, later in this course we're going to talk about joins. How we use them and what they're for, but it's important to know what subqueries are and how they fit into the grand scheme of things. This is just one of many techniques we can use to start to combine information together from multiple tables. We will continue on with some additional ways to combine data in the next few videos.