Explains an alternative method to retrieve data from more than one table, using nested queries or subqueries, and explains when this is a reasonable option
- [Narrator] We've seen this sort of SELECT statement countless times now. Now we're going to look at something that is actually equivalent but certainly looks a bit different. And we say SELECT f.first_name from, and then we're going to write a subquery in brackets. And we say (SELECT first_name from actor). And we call that whole result set, we give it an alias of f.
Now that's returned us the same 200 rows. Now obviously, this is a contrived example, and you would generally just use this smaller query in preference, but the syntax is useful. So let's look at what's going on here. If you come across this sort of query, you should always make sense of it from the inside out. And when I say inside, I mean start off by looking at anything in parentheses. So what this is doing, it's selecting the first_name field from actor, and that result set is being given an alias of f.
And then we're selecting first_name from the result set. It's similar in some ways to the IN syntax that we saw in the last video. If we didn't use the alias here, the query would fall over. And under a long list of terrifying errors, it would say, "Every derived table must have its own alias." So as far as MySQL is concerned, this subquery here is giving us a derived table as f. Now what if we gave the fieldname in the subquery an alias? So we've given an alias to the result set.
Now I'm going to say first_name as fn. Now if we run that, MySQL can't find the column at all. It says, "Unknown column in field list." And that's because it can't see the original table at all. It can only see the result set called f. So if we gave the fieldname an alias, we would need to use that alias to actually return the data. Now in practice, nobody would use this sort of query in this example because the same result can be achieved more efficiently with the first statement.
But where you will see this pop up is, in particular, where you're running calculations on different tables and you want to return multiple calculations from multiple tables into one result set.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries