SQL Basics - Chapter 11

01 - Subquery

Subqueries are queries nested inside another query
They are useful when information from multiple tables is needed
A subquery can appear in the SELECT, FROM, WHERE or HAVING clause of another query

SELECT <column1>, <column2>
FROM   <table1>
WHERE  <column3> IN ( SELECT <column4>
                    FROM   <table2> 
                    WHERE  <column5> IN ( SELECT <column6>
                                         FROM   <table3>
                                         WHERE  <column7> = "Value" ));

Enclose each subquery in parentheses
Evaluation starts with the innermost query
A subquery used with IN provides the set of values to be checked, like the list required by the IN operator

A subquery used in this way must select only one field or column.

02 - Filtering with a Subquery in WHERE

Test the subquery before the main query
Break a complex question into smaller problems
Start with a part you can already answer with a query. This often becomes the subquery
Work towards the main question: what do you want to display?
Make the query as readable as possible

Place a subquery in WHERE when its result is needed to filter information

SELECT     <column01>,
           <column02>
FROM       <table01>  
WHERE      <column02> IN (
                         SELECT  MAX(column03)
                         FROM    <table02>
                        );

Return the product ID and price of the most expensive product

TIP

Select the highest product price from OrderItems: MAX(item_price) — subquery
Select prod_id and item_price from OrderItems — main query
Filter in the main query's WHERE clause: item_price IN (subquery)
Remove duplicate prod_id values with DISTINCT

Answer

-- Oracle / MySQL / SQL Server
SELECT   prod_id,
         prod_price
FROM     Products  
WHERE    prod_price IN (
                        SELECT MAX(prod_price)
                        FROM Products
                       );

prod_id	prod_price
BR03	11.99

Select the product ID, name and price for products costing more than the average product price

TIP

Select AVG(prod_price) from Products — subquery
Select the product ID, name and price from Products — main query
Filter in the main query's WHERE clause: prod_price > (subquery)

Answer

-- Oracle / MySQL / SQL Server
SELECT   prod_id,
         prod_name,
         prod_price
FROM     Products
WHERE    prod_price > ( SELECT AVG(prod_price)
                        FROM Products )
ORDER BY prod_price;

prod_id	prod_name	prod_price
BR02	12 inch teddy bear	8.99
RYL01	King doll	9.49
RYL02	Queen doll	9.49
BR03	18 inch teddy bear	11.99

03 - A Subquery in SELECT

When combining two tables, use fully qualified column names where necessary
A fully qualified column name consists of the table name, a dot and the column name
Fully qualified names distinguish columns with the same name in different tables

Products.prod_id 

OrderItems.prod_id

Using fully qualified column names makes a query involving multiple tables clearer

A subquery in SELECT can display information from another table

SELECT    <table1>.<column1>,
          <table1>.<column2>,
          ( 
            SELECT COUNT(*) 
            FROM   <table2>
            WHERE  <table2>.<column1> = <table1>.<column1>
          ) AS NewColumnName
FROM      <table1>
ORDER BY  <table1>.<column1>;

Select each customer's name and state, and the number of orders they have placed

TIP

Select the customer name and state from Customers
Count the orders: COUNT(*) FROM Orders
Match the two tables in the subquery's WHERE clause

Answer

-- Oracle / MySQL / SQL Server
SELECT    Customers.cust_name,
          Customers.cust_state,
          ( SELECT COUNT(*)
            FROM   Orders
            WHERE  Orders.cust_id = Customers.cust_id ) AS orders
FROM      Customers
ORDER BY  Customers.cust_name;

cust_name	cust_state	orders
Fun4All	IN	1
Fun4All	AZ	1
Kids Place	OH	0
The Toy Store	IL	1
Village Toys	MI	2

EXERCISES 46 TO 50

Select customers living in the same state as a vendor (2)

Select all orders placed by customers whose email address is unknown (1)

Select the customer names for all orders placed by customers whose email address is unknown (1)

TIP

First select all cust_id values from Customers where the email field is NULL — innermost subquery
Select cust_id values from Orders WHERE Orders.cust_id is IN the previous result — subquery
Select customer names from Customers WHERE Customers.cust_id is IN the previous result — main query

Select all product names from Products and add a column containing the total quantity sold (9)

TIP

Calculate SUM(quantity) from OrderItems — subquery
Select the product names from Products — main query
Add a WHERE clause to the subquery matching prod_id in the two tables (... = ...)
Give the new column a suitable name

Select the customer IDs and the total amount of each order they placed (5)

TIP

Calculate the sum of quantity * item_price from OrderItems — subquery
Select cust_id from Orders — main query
Add a WHERE clause to the subquery matching order_num in the two tables (... = ...)
Give the new column a suitable name