11.1 Subquery

Subqueries, as the name implies, are queries nested in other queries.

Create the following tables first:

###############################
# Role: Store all customer information  		 
# Customer ID unique to cust_id  	 
# Customer name cust_name      		 
# Customer address for cust_address 		 
# Customer City for cust_city	      	 
# Customer State of cust_state     		 
# Postal Code of Customer cust_zip	 
# Customer Country for cust_country		 
# Contact name of cust_contact customer		 
# Contact email address of cust_email customer	 
###############################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;


##########################################
# Role: Store customer orders   		      	         
# order_num Unique Order Number     			         
# order_date Order Date            		         
# Cust_id Order Customer ID (cust_id in the associated customers table)  
##########################################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num),
  CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id)
) ENGINE=InnoDB;

#########################################
# Role: Actual items in each order   		       
# Order_num order number (order_num associated with the orders table)     
# order_item Order item number (order in an order)             
# Prod_id product ID (prod_id in the associated products table)      
# quantity Item quantity			      
# item_price item price			      
#########################################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item) ,
  CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num) ,
  CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id)
) ENGINE=InnoDB;

Insert data:

# products
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');

# orders
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);

# orderitems
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

The orders table stores customer orders, the orderitems table stores the actual item information in each order, and the customers table stores customer information.

Now, suppose you need to list all customers ordering TNT2, how should you retrieve it?Here are the steps:

• Retrieve the number of all orders containing item TNT2.
• Retrieve the ID s of all customers with the order numbers listed in the previous step.
• Retrieve customer information for all customer ID s returned in the previous step.

Each of these steps can be executed as a separate query:

1. Retrieve the number of all orders containing item TNT2:

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

Output:

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

1. Retrieve the ID s of all customers with the order numbers listed in the previous step:

SELECT cust_id
FROM orders
WHERE order_num IN(20005, 20007);

The result returned by one SELECT statement can be used in the WHERE clause of another SELECT statement.So turn the first query above into a subquery that combines two queries:

SELECT cust_id
FROM orders
WHERE order_num IN(
    SELECT order_num
    FROM orderitems
    WHERE prod_id = 'TNT2');

Output:

+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

Interpretation: Subqueries are always processed from the inside out.In the above SQL statement, execute the following statement first:

SELECT order_num
    FROM orderitems
    WHERE prod_id = 'TNT2';

The order number of INT2 is returned, and then the order number is queried externally as a condition of the WHERE clause.

3. Now we have the ID of all the customers who ordered INT2.The next step is to retrieve the customer information for these customer IDs.

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN(10001,10004);

Our subqueries can continue to be nested:

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN(
    SELECT cust_id
    FROM orders
    WHERE order_num IN(
        SELECT order_num
        FROM orderitems
        WHERE prod_id = 'TNT2'));

Output:

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

Using a subquery in a WHERE clause enables you to write SQL statements that are powerful and flexible in addition to functionality.There are no restrictions on the data that can be nested in subqueries, but in practice, too many subqueries cannot be nested due to performance constraints.

Subqueries are generally used in conjunction with the IN operator, but can also be used to test for equals (=), not equals (<>), and so on.

11.2 Use subqueries as calculated fields

Another way to use subqueries is to create computed fields.Suppose you need to display the total number of orders for each customer in the customers table.The order and the corresponding customer ID are stored in the orders table.Here are the steps:

• Retrieve the list of customers from the customers table.
• For each customer retrieved, count the number of orders in the orders table.

Rows in a table can be counted using SELECT COUNT(*) and a specific customer ID can be filtered by providing a WHERE clause to count only orders from that customer.For example:

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;

In order to perform COUNT() calculations for each customer, COUNT() should be treated as a subquery.The following:

SELECT cust_name, 
        cust_state,
        (SELECT COUNT(*)
           FROM orders
           WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

In the above, there are orders.cust_id=customers.cust_id.This sentence is a related subquery, which refers to a subquery of an external table query.Also, we use fully qualified names to better distinguish the same column names of the two tables.

11.3 Subquery Skills

• First, build and test the innermost query;
• The outer query is then built and tested with hard-coded data and embedded only after it is confirmed to be normal.
• Best, test the combined subqueries again, and repeat these steps for each query you want to add.

Summary: Describes what subqueries are and how to use them.The most common use of subqueries is in the IN operator of the WHERE clause and to populate computed columns.