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)

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)

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)

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.

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

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.

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

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;

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.

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

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;