Lecture 11: Multi-Table Selects & Intro to Subqueries PDF

Lecture 11: Multi-Table Selects & Intro to Subqueries CS 195 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Multi table select Subqueries Joins vs Subqueries Other uses of Subqueri...

Lecture 11: Multi-Table Selects & Intro to Subqueries CS 195 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Multi table select Subqueries Joins vs Subqueries Other uses of Subqueries WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Overview You can join more than 2 tables together When doing this you will have multiple JOIN and ON clauses WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with product associated to that order. 1. Figure out the tables that we are trying to get information from and how they connect 2. Figure out what type of joins are needed to get the information 3. Start with a parent table (Ex: Customer Orders) 4. Find how to get to the Product table and create a join with intermediary table 5. Next add on a join to the third table product. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with product associated to that order. Why wouldn’t I just join product to order_item OR cust_order to order_item? WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with product associated to that order. 1. Figure out the tables that we are trying to get information from and how they connect 2. Figure out what type of joins are needed to get the information 3. Start with a parent table (Ex: Customer Orders) 4. Find how to get to the Product table and create a join with intermediary table 5. Next add on a join to the third table product. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with the products associated to that order. In order for an order_item to exists there needs to be a product associated to it So if we are trying to find all the customer orders and their products then we just want inner joins WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with products associated to that order. 1. Figure out the tables that we are trying to get information from and how they connect 2. Figure out what type of joins are needed to get the information 3. Start with a parent table (Ex: Customer Orders) 4. Find how to get to the Product table and create a join with intermediary table 5. Next add on a join to the third table product. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with products associated to that order. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with products associated to that order. 1. Figure out the tables that we are trying to get information from and how they connect 2. Figure out what type of joins are needed to get the information 3. Start with a parent table (Ex: Customer Orders) 4. Find how to get to the Product table and create a join with intermediary table 5. Next add on a join to the third table product. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with products associated to that order. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with products associated to that order. 1. Figure out the tables that we are trying to get information from and how they connect 2. Figure out what type of joins are needed to get the information 3. Start with a parent table (Ex: Customer Orders) 4. Find how to get to the Product table and create a join with intermediary table 5. Next add on a join to the third table product. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Example We want to get the customer orders with products associated to that order. Notice: the ON still follows parent.key = child.key WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Mix Example You can mix inner, left and right joins Just make sure that you first separate out the joins into single joins to understand what data you are returning Then slowly start to combine the joins WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Mix Example1 We want to get all customer orders with products associated to that order, if any We want all customer orders regardless if there is an order item or a product. This means we will left join cust_order to order_item In order for there to be an order_item we have to have a product which means we need to left join to product as well. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Mix Example1 We want to get all customer orders with products associated to that order, if any WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Mix Example1 We want to get all customer orders with products associated to that order, if any Notice if we did an inner to product that we don’t get all the customers. That is because it is saying find all products and their matching orders but customer order 3 doesn’t have orders so it gets dropped WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Mix Example2 We want to get all products associated to a customer order, if any We want to get all products regardless if there is a customer order. We will right join to product as we want all products. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Select Mix Example2 We want to get all products associated to a customer order, if any WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Multi Table Join Zoo Database Show what species each vet has treated and what their specialty is Show what specific event Emily Johnson attended? Show all events (name) and any possible animals(name) that are part of them WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Multi table select Subqueries Joins vs Subqueries Other uses of Subqueries WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case A subquery is a SELECT statement that’s coded within another SQL statement. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case It can return a single value, a list of values (a result set that has a single column, or a table of values) Can be introduced anywhere a single value, a list of values or a table is allowed WHERE, HAVING, FROM, SELECT WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case List names of all the customers who are in the same city as Freddi WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case List names of all the customers who are in the same city as Freddi Two Ways: 1. Go and lookup Freddi in the database and see what state he is in. Create a query looking for that state 2. Use a subquery to dynamically go and look up Freddi’s state and then find all the customers in that state WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case: Option 1 List names of all the customers who are in the same city as Freddi WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case: Option 2 Instead, we can create a subquery to get this information dynamically 1st query to find Freddi’s state 2nd query to find all states that match the results of the 1st query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case: Option 2 Instead, we can create a subquery to get this information dynamically 1st query to find Freddi’s state WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case: Option 2 Instead, we can create a subquery to get this information dynamically 1st query to find Freddi’s state 2nd query to find all states that match the results of the 1st query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Use Case List names of all the customers who are in the same city as Freddi 2nd query 1st query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) The first SELECT will be the fields you want to return in the query These will be the only fields displayed (regardless of other selects/tables) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) From is associated to the fields from the first SELECT WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) First Where: This is the column that you are trying to populate with the second query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) Similar conditions that you use in the WHERE clause =, IN, NOT IN, !=, comparison WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) Your subquery needs to be within () WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) The second SELECT will have the same column name that you used in your first WHERE clause This column will not show unless it was in the first SELECT statement WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) This is the table that column1 can be found It can be the same table as the first FROM or It can be another table that has column1 in it WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Syntax WHERE column1 Example [operator]( SELECT column1 FROM table2 WHERE query condition) Second WHERE: this is the condition that you are trying to use to populate column1 with WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries SELECT [fields to display] FROM table1 Understanding WHERE column1 [operator]( SELECT column1 FROM table2 WHERE query condition) The inner query is evaluated first The value returned is used to evaluate the outer query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Example List names of all the customers who are in the same city as Freddi Evaluated First WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Example List names of all the customers who are in the same city as Freddi Results from this query are put into the second query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Example List names of all the customers who are in the same city as Freddi Must be the same field WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Example List names of all the customers who are in the same city as Freddi Second query executed with the result from the first query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Example List the product with the highest unit price Gets the max unit price WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Example List the product with the highest unit price Uses the returned max unit price to limit the products down WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity Finds the orders with quantity > 3 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity Why are we using IN WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity Gets the customer_ids that are in the list order_ids that were returned from the first query WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity Why are we using IN WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity Gets the customers that are in the customer_id list This should only be customers that have a quantity > 3 in order WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity The field that is in your next Select has to be the same field that was used in the clause that is calling the subquery WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity Table names can be different as long as the field in the select statement is found in that table WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity Subqueries have to be in parathesis. Parathesis order and placement does matter WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Subqueries Zoo Database Find all animals that are the same species as Peanut Find what events are greater than or equal to the average price? Find what animals are feed at 9am WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Multi table select Subqueries Joins vs Subqueries Other uses of Subqueries WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries Overview Most subqueries can be restated as joins, and most joins can be restated as subqueries as shown by another SELECT statement. In generally, pick the technique that results in the most readable code if there is no reason to pick one over the other. WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries Use Case List customer_id, first_name, last_name that have an order Can do this by doing a: 1. Inner join between customer and cust_order 2. Subquery WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries Use Case - JOIN List customer_id, first_name, last_name that have an order WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries Use Case - SUBQUERY List customer_id, first_name, last_name that have an order WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries - Advantages Join Subquery The SELECT clause of a join Pass aggregate values to the can include columns from main query both/all tables When you only need to show Tend to be more intuitive results from one table when it uses an existing relationship between two Long complex queries can tables sometimes be easier to code using subqueries WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries - Disadvantage Join Subquery More memory intensive If you use a subquery in the a WHERE clause, the results can’t be included in the final result set WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries Typically, when to use subquery? When data from only one table is to be listed using criteria involving other tables, a subquery is more logical and more efficient than joins Joins also give all records from the two tables. If don’t want this, you need to use DISTINCT inside join statement Instead, you can use a subquery WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Nested Subqueries Example List name of customers that have orders with more than 3 quantity WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries Zoo Database Find what animals are feed at 9am Show animals that have had dental care done WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Joins vs Subqueries Zoo Database Find what animals are feed at 9am Show animals that have had dental care done What if I wanted to see the notes? WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Multi table select Subqueries Joins vs Subqueries Other uses of Subqueries WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Uses of Subqueries Overview Will learn next class but common uses cases for subqueries Insert records into a table Update records that are already in a table Delete records that are already in a table WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Uses of Subqueries Overview Will learn next class but common uses cases for subqueries Insert records into a table Insert a new record into order_item where the order_id is Naseby WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Uses of Subqueries Overview Will learn next class but common uses cases for subqueries Update records that are already in a table Increase by 10% the unit price for products with more than 75 items in stock WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Uses of Subqueries Overview Will learn next class but common uses cases for subqueries Delete records that are already in a table Delete any not ordered products WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO