Subject: [[Structured Query Language]]
Topics: #sql #basics #query #subquery
# Subqueries
A subquery is a query that is nested inside another query. It allows you to retrieve data from one or more tables and use it as input for another query. Subqueries can be used in the SELECT, INSERT, UPDATE, and DELETE statements.
### Subquery Structure
A subquery consists of a SELECT statement enclosed in parentheses. Here is the basic structure of a subquery:
```sql
SELECT column_name(s)
FROM table_name
WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);
```
### Example
Let's say we have two tables, `customers` and `orders`. The
`customers` table has the following columns: `customer_id`, `customer_name`, and `customer_email`.
`orders` table has the following columns: `order_id`, `customer_id`, `order_date`, and `order_amount`.
We want to retrieve the names of customers who have placed orders with an amount greater than $100. We can use a subquery to accomplish this:
**Table Name: customers**
|Column Name|Data Type|Constraints|
|---|---|---|
|customer_id|SERIAL|PRIMARY KEY|
|customer_name|VARCHAR(50)|NOT NULL|
|customer_email|VARCHAR(50)|NOT NULL|
**Table Name: orders**
|Column Name|Data Type|Constraints|
|---|---|---|
|order_id|SERIAL|PRIMARY KEY|
|customer_id|INTEGER|NOT NULL|
|order_date|DATE|NOT NULL|
|order_amount|DECIMAL(10,2)|NOT NULL|
**customers Table**
|customer_name|customer_email|
|---|---|
|John Doe|[mailto:
[email protected]](mailto:
[email protected])|
|Jane Smith|[mailto:
[email protected]](mailto:
[email protected])|
|Bob Johnson|[mailto:
[email protected]](mailto:
[email protected])|
**orders Table**
|customer_id|order_date|order_amount|
|---|---|---|
|1|@January 1, 2021|50.00|
|1|@February 1, 2021|75.00|
|2|@February 15, 2021|125.00|
|3|@March 1, 2021|200.00|
```sql
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
customer_email VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
order_amount DECIMAL(10,2) NOT NULL
);
INSERT INTO customers (customer_name, customer_email) VALUES
('John Doe', '
[email protected]'),
('Jane Smith', '
[email protected]'),
('Bob Johnson', '
[email protected]');
INSERT INTO orders (customer_id, order_date, order_amount) VALUES
(1, '2021-01-01', 50.00),
(1, '2021-02-01', 75.00),
(2, '2021-02-15', 125.00),
(3, '2021-03-01', 200.00);
-- subquery
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_amount > 100);
```
The result of this query would be:
customer_name
---
Jane Smith
---
Bob Johnson
---
### Multiple Subqueries in SELECT Statement
Let's say we want to retrieve the customer_name, email, and the total number of orders they have placed. We can use multiple subqueries in the SELECT statement to accomplish this:
```sql
SELECT customer_name, customer_email,
(SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) as total_orders,
(SELECT SUM(order_amount) FROM orders WHERE orders.customer_id = customers.customer_id) as total_order_amount
FROM customers;
```
The result of this query would be:
|customer_name|customer_email|total_orders|total_order_amount|
|---|---|---|---|
|John Doe|[mailto:
[email protected]](mailto:
[email protected])|2|125.00|
|Jane Smith|[mailto:
[email protected]](mailto:
[email protected])|1|125.00|
|Bob Johnson|[mailto:
[email protected]](mailto:
[email protected])|1|200.00|
### Subquery in FROM Statement
Let's say we want to retrieve the customer name and the average order amount for customers who have placed orders. We can use a subquery in the FROM statement to accomplish this:
```sql
SELECT customer_name, AVG(order_amount) as avg_order_amount
FROM (
SELECT *
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
) AS cust_orders
GROUP BY customer_name;
```
The result of this query would be:
|customer_name|avg_order_amount|
|---|---|
|John Doe|62.50|
|Jane Smith|125.00|
|Bob Johnson|200.00|
### Subquery in HAVING Statement
Let's say we want to retrieve the customer name and the total number of orders for customers who have placed more than one order. We can use a subquery in the HAVING statement to accomplish this:
```sql
SELECT customer_name, COUNT(*) as total_orders
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customer_name
HAVING COUNT(*) > 1;
```
The result of this query would be:
|customer_name|total_orders|
|---|---|
|John Doe|2|
## PRACTICE
- What is the customer name and email of customers who have placed orders with an amount greater than $100?
```
SELECT customer_name, customer_email
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_amount > 100);
```
- What is the customer name, email, total number of orders they have placed, and total order amount for each customer?
```
SELECT customer_name, customer_email,
(SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) as total_orders,
(SELECT SUM(order_amount) FROM orders WHERE orders.customer_id = customers.customer_id) as total_order_amount
FROM customers;
```
- What is the customer name and the average order amount for customers who have placed orders?
```
SELECT customer_name, AVG(order_amount) as avg_order_amount
FROM (
SELECT *
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
) AS cust_orders
GROUP BY customer_name;
```
- What is the customer name and the total number of orders for customers who have placed more than one order?
```
SELECT customer_name, COUNT(*) as total_orders
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customer_name
HAVING COUNT(*) > 1;
```