# 7. Subquery

### Subquery

**(*****write a query nested inside another query*****)**

* a subquery is a query nested inside another query such as `SELECT, INSERT, DELETE & UPDATE`

#### Example 1 (get films whose rental rate is higher than average rental rate)

```sql
SELECT
	film_id,
	title,
	rental_rate
FROM
	film
WHERE
	rental_rate > (
		SELECT
			AVG (rental_rate)
		FROM
			film
	);
```

#### Example 2 (get films that have returned rate between two dates)

```sql
SELECT
	film_id,
	title
FROM
	film
WHERE
	film_id IN (
		SELECT
			inventory.film_id
		FROM
			rental
		INNER JOIN inventory USING (inventory_id)
		WHERE
			return_date BETWEEN '2005-05-29' AND '2005-05-30'
	);
```

#### Example 3 (get customers who have paid)

```sql
SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	EXISTS (
		SELECT
			1
		FROM
			payment
		WHERE
			payment.customer_id = customer.customer_id;
	);
```

***

### ANY

**(*****retrieve data by comparing a value with a set of values returned by a subquery.*****)**

* The subquery must return exactly one column.
* The ANY operator must be preceded by one of the following comparison operator =, <=, >, <, > and <>
* The ANY operator returns true if any value of the subquery meets the condition, otherwise, it returns false.
* The = ANY is equivalent to IN operator.

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FATrfwMKErhozIA5SXITT%2Fimage.png?alt=media&#x26;token=827bfdae-c551-4349-8929-590995d16453" alt=""><figcaption></figcaption></figure>

#### Example 1 (get films whose category is either Action or Drama)

```sql
SELECT
	title,
	category_id
FROM
	film
INNER JOIN film_category
	USING(film_id)
WHERE
	category_id = ANY(
		SELECT
			category_id
		FROM
			category
		WHERE
			NAME = 'Action'
			OR
			NAME = 'Drama'
	);
```

***

### ALL

**(*****query data by comparing a value with a list of values returned by a subquery.*****)**

With the assumption that the subquery returns some rows, the ALL operator works as follows:

1. `column_name > ALL (subquery)` the expression evaluates to true if a value is greater than the biggest value returned by the subquery.
2. `column_name >= ALL (subquery)` the expression evaluates to true if a value is greater than or equal to the biggest value returned by the subquery.
3. `column_name < ALL (subquery)` the expression evaluates to true if a value is less than the smallest value returned by the subquery.
4. `column_name <= ALL (subquery)` the expression evaluates to true if a value is less than or equal to the smallest value returned by the subquery.
5. `column_name = ALL (subquery)` the expression evaluates to true if a value is equal to any value returned by the subquery.
6. `column_name != ALL (subquery)` the expression evaluates to true if a value is not equal to any value returned by the subquery.

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FEpWNO8Zs8TvVF5I5PHF0%2Fimage.png?alt=media&#x26;token=a0cee98a-0560-4517-9bb8-13e7f4522beb" alt=""><figcaption></figcaption></figure>

#### Example 1 (find all films whose lengths are greater than the list of the average lengths)

```sql
SELECT
	film_id,
	title,
	length
FROM
	film
WHERE
	length > ALL (
		SELECT
			ROUND(AVG(length), 2) avg_length
		FROM
			film
		GROUP BY
			rating
	)
ORDER BY
	length;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FOiBZsmHCqorbUdKcFi1M%2Fimage.png?alt=media&#x26;token=7f91a181-7c0f-4ca7-a16c-9020d6e91f27" alt=""><figcaption></figcaption></figure>

### EXISTS

**(*****check for the existence of rows returned by a subquery.*****)**

* If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result is of EXISTS is false
  * If the subquery returns NULL, EXISTS returns true
* The result of EXISTS operator depends on whether any row returned by the subquery, and not on the row contents. Therefore, columns that appear on the SELECT clause of the subquery are not important.

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2Fa73142eksKAIdcJ9jGz5%2Fimage.png?alt=media&#x26;token=90571009-b307-44bf-95e1-0dd0c34f988c" alt=""><figcaption></figcaption></figure>

#### Example 1 (Find customers who have at least one payment whose amount is greater than 11)

```sql
SELECT
	customer_id,
	first_name,
	last_name
FROM
	customer c
WHERE
	EXISTS (
		SELECT
			1
		FROM
			payment p
		WHERE
			c.customer_id = p.customer_id
			AND
			amount > 11
	)
ORDER BY
	first_name,
	last_name;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2Fn2zZn9S65oV9niWAzWot%2Fimage.png?alt=media&#x26;token=917c22f6-2072-40de-9b75-04c80fa30232" alt=""><figcaption></figcaption></figure>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://notes.nomanaziz.me/development/database/sql/basics/7.-subquery.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.