Explains an alternative (generally less recommended) method to retrieve data from one table based on a filter placed on another table
- [Instructor] We've seen the IN keyword already for filtering. So we can write for example select star from actor, where field name is IN and then we supply a list of matching values. Or actually, we can put a second select statement into these brackets so that we are returning data from one table based on records in another. Generally, it's neater and quicker to use joins but there are times when you might want to use this method, so let's give it a go.
We're going to get all the rental details where the customer name is Jennifer. So what we'll do, select star from rental, and we put the rental table first because this is the one that we want to return data from. And then we say where customer ID is in, and we delete all of those and we write a whole new select statement, select customer ID from customer where first name is Jennifer.
And that's returned as 28 rows there, so there have been 28 rentals made by a customer with a first name of Jennifer. So we're filtering customer ID based on customer name, and then we return that in a format that the first query can understand because we're returning customer IDs and then matching customer IDs to them. You could add a where clause, another where clause here as well outside of the brackets, and that would refer to the rental table.
You could even add another IN statement and filter with data from a different table as well, although that would be a lot slower than using a join, but what you can't do with this method is display results from multiple tables. So we've only returned data here from the rental table, and we haven't got anything from the customer table.
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