Subquery and Exist - rFronteddu/general_wiki GitHub Wiki

Subquery

A sub query allows you to construct complex queries, essentially performing a query on the results of another query. The syntax involves two SELECT statements.

Suppose you want to get a list of students who scored better than the average grade:

SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores)

Subqueries can operate on separate tables too:

SELECT student, grade
FROM test_scores
WHERE student IN (SELECT student FROM honor_roll_table)

SELECT student, grade
FROM test_scores
WHERE student IN (SELECT student FROM honor_roll_table)

If the subquery returns multiple values you must use the IN keyword

SELECT film_id, title
FROM film
WHERE film_id IN
(SELECT inventory.film_id
FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30')
ORDER BY film_id

Exists

EXIST is used to check if there is any value. You can check if something doesn't exist by adding the NOT operator in fromt of EXISTS.

SELECT first_name, last_name
FROM customer AS c
WHERE NOT EXISTS
(SELECT * FROM payment as p 
WHERE p.customer_id = c.customer_id
AND amount > 11)